[mysql-php] mysqlimport error
Hello list I'm trying mysqlimport --local -d --fields-enclosed-by=| --fields-terminated-by=; - -lines-terminted-by=\n -unikos -p mydb c://temp//programs.txt but i get an Error: File 'c:/temp/programs.txt' not found (Errcode: 2), when using table: programs What I'm doing wrong? RH-9 - APACHE v2 - mysql 3.23.54 Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error on Outfile
I just encountered this yesterday on my Macintosh, and it turned out that user mysql did not have permission to write to the destination folder. I simply had to change the permissions on that folder to include Read Write access to user mysql. -John on 6/1/04 8:09 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: input: select firstname,middlename,lastname,county,state,dob,ssn into outfile '/Users/user/temp/test.csv' fields terminated by ',' lines terminated by '\n' from iffinet.subjects where client_id='1' and ostatus='complete'; output: ERROR 1: Can't create/write to file '/Users/timbest/test.csv' (Errcode: 13) I¹ve granted full rights to anyone who accesses /Users/user/temp. Any ideas as to what I can do to fix this? VR/Tim Best IT cell: 504-231-1084 fax: 206-338-6162 [EMAIL PROTECTED] http://www.best-it.biz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext in boolean mode with utf8
Hi All!
May be anyone can help in this situation:
Version: 4.1.2-alpha-nightly-20040523-log
OS : Linux
Show create table TEST:
CREATE TABLE `TEST` (
`ID` int(10) unsigned NOT NULL auto_increment,
`saturs` char(255) default NULL,
PRIMARY KEY (`ID`),
FULLTEXT KEY `sat` (`saturs`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
insert into TEST (ID,saturs) values (1, _cp1257 'pa-r par par')
insert into TEST (ID,saturs) values (2, _cp1257 'par par par')
select * from TEST:
ID saturs
1 pÄ?r par par
2 par par par
select * from TEST where match(saturs) against('par' in boolean mode) :
no rows
_
So while no rows in last search?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting a different database to mysql
Gidday. Is there anyway to convert an Advanced Revelations/Open Insight database into a mySQL database. Cheers, Glenn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
There's got ot be something else wrong. I've got the exact same problem and
I have done a chmod 666 on the directory I am trying to write to with no
luck. I am running v4.0.15
/T
on 6/1/04 7:06, Egor Egorov at [EMAIL PROTECTED] wrote:
John Mistler [EMAIL PROTECTED] wrote:
Would there be any obvious reasons for its inability to access the desktop?
I have used that desktop directory many times before in UNIX.
Check permissions on that dir. It must be writeable by the user you run mysqld
as ('mysql' by default).
on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote:
John Mistler [EMAIL PROTECTED] wrote:
Can anyone guess why I am getting this error:
ERROR 1: Can't create/write to file
'/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13)
when using this statement:
mysql SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM
theTable WHERE column1 != 'thisString';
? I am logging in as root for the test.
It means that MySQL server can not access to the /Users/johnmistler/Desktop/
directory.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Short Passwords in 4.1.2
Greg Willits [EMAIL PROTECTED] wrote: I'm trying to force 4.1.2 to use the old short passwords for now during some experimental stages. The discussion here (specifically the fourth set of bullets): http://dev.mysql.com/doc/mysql/en/Password_hashing.html and, this paragraph: The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to widen the Password column. led me to think that I could modify the user table and set the width of the Password column to varchar(16) and based on the narrow column 4.1.2 would always default to creating the old passwords. However, GRANT statements are creating passwords that start with * so, even though they're chopped off at 16, they're obviously still the new format. - Sidebar: interesting--mysql 4.0 creates host, user, password columns as binary but 4.1.2 does not? - Can 4.1.2 be forced to used old passwords this way? Do I need to modify the startup script to include --old-passwords? Yup, you must run MySQL server with --old-passwords option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Equijoin on non-equality barfs!
Mailing List Receiver [EMAIL PROTECTED] wrote: mysql select bademail.email from bademail,noemail where bademail.email noemail.email; bademail has about 30,000 rows and noemail has 13,000. This, by the way, does a good job of bringing your system to its knees! You need something like: SELECT bademail.email FROM bademail LEFT JOIN noemail ON bademail.email=noemail.email WHERE noemail.email IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Converting a different database to mysql
Hello, If the db has an ODBC driver then you can try SQLyog from www.webyog.com Karam --- Glenn McCord [EMAIL PROTECTED] wrote: Gidday. Is there anyway to convert an Advanced Revelations/Open Insight database into a mySQL database. Cheers, Glenn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ 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: last_insert_id() value not updated
paqogomez [EMAIL PROTECTED] wrote: I am trying to build a stored procedure in v. 5. This is what I have so far. delimiter | create procedure get_id(out oid int) begin insert into mercury.merchant (name) values(null); select last_insert_id() into @mid; insert into mercury.customers( address1 ) values (null); select last_insert_id() into @cid; insert into mercury.item_information( description ) values (null); select last_insert_id() into @iiid; insert into mercury.fee_information ( delivery_fee ) values (null); select last_insert_id() into @fiid; insert into mercury.orders ( customer_id, merchant_id, item_information_id, fee_information_id ) values ( @cid, @mid, @iiid, @fiid ); select last_insert_id() into oid; end | My problem is that last_insert_id() is not updated for each insert statement, only on the connection. The documentation says something about using insert ignore, but I couldnt get this to give me any different results. Is there any way to do what I want here and still have the procedure be transaction safe? This issue is related to the known bug. It's already fixed: http://bugs.mysql.com/bug.php?id=3117 -- 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: RI enforcement and m2m relationships.
I don't see how you can possibly hope to satisfy two mutually dependent constraints at the exact same time. It's a chicken-and-egg scenario. If you can't create your vacancy record first (to get its PK value) you won't be able to create the new record in your supervision table. Most people solve this dependency dilemma with either application-layer processing or by creating a stored procedure (new to MySQL 5.x). Many people also wrap the entire process in a transaction (supported in InnoDB) so that if you somehow fail to create the new supervision record then you would be able to rollback the transaction (un-creating the vacancy record), leaving your database in a consistent state (no vacancy would exist unless it has a corresponding supervision record). It's not hard to do what you want, but it requires logic external to the storage engine itself to enforce that kind of relationship. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine eifion herbert (IAH-C) To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: bsrc.ac.uk Fax to: Subject: RI enforcement and m2m relationships. 06/02/2004 06:45 AM Hi all, I'm developing a database to store job adverts to appear on a company website. Each vacancy has at least one supervisor, but may have more than one, and a supervisor may be responsible for multiple vacancies. I've resolved the vacancy--supervisor many-to-many relationship in the normal(?) way of having a table called supervision inbetween them with two columns, the Ref of the vacancy and the ID of the supervisor. Both these columns make up the pri key in the supervision table, and are also foreign keys referencing the pri keys in the vacancy and supervisor table. Unfortunately this allows a vacancy to be added to the database without a supervisor being assigned. Is there a way in mySQL4 or Perl to enforce this without creating an insertion anomaly? Cheers Eifion -- 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]
Sub query on mySQL 4.0.18
Dear my friends... I don#t understand why subquery on my MySQL 4.0.18-Mas does not valid. Here what I have done. [EMAIL PROTECTED]:~ mysql -h 192.168.23.1 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 354744 to server version: 4.0.18-Max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use bernstein; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from salesreport where custid in (select custid from appointment where done='N'); ERROR 1064: 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 'select custid from appointment where done='N')' at line 1 mysql Please tell me Thank you very much in advance. __ 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: Sub query on mySQL 4.0.18
I don#t understand why subquery on my MySQL 4.0.18-Mas does not valid. Simple reason: v. 4.0.18 does not support subqueries yet. Use 4.1 or later. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: R: R: R: R: query string too long?
Test it on 4.1.2 and let me know if these queries will crash MySQL server. Yes, the query (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM PARTITIONED_A_1_1 AS PARTITIONED, PARTITIONED_B_1 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) crashes mysql 4.1.2. Executing the same query, but with less unions (at least one less) does not crash mysql. These are the tables (NO DATA, mysql crashes even with no data): CREATE TABLE IF NOT EXISTS PARTITIONED_A_1_1 ( ID1 int(10) unsigned NOT NULL DEFAULT '0' , ID2 datetime NOT NULL DEFAULT '-00-00 00:00:00' , DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2) ); CREATE TABLE IF NOT EXISTS PARTITIONED_B_1 ( ID int(3) unsigned NOT NULL DEFAULT '0' , DATA1 timestamp DEFAULT '-00-00 00:00:00' , PRIMARY KEY (ID) ); I'm sure about the version: Version: '4.1.2-alpha-standard-log' socket: '/tmp/mysql3.sock' port: 3310 This is the error: 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=268435456 read_buffer_size=1044480 max_used_connections=2 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 466543 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. This is my my.cnf: # The following options will be passed to all MySQL clients [client] port= 3310 socket = /tmp/mysql3.sock host= 192.168.0.252 [mysqld] user = mysql socket = /tmp/mysql3.sock port= 3310 pid-file = /opt/mysql4.1.2/hostname.pid datadir = /opt/mysql4.1.2/data binlog-do-db = none character-sets-dir=/opt/mysql4.1.2/share/mysql language=/opt/mysql4.1.2/share/mysql/english/ old-passwords skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=56 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id = 1 # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=64M #set-variable = bdb_max_lock=10 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:10M:autoextend innodb_data_home_dir =/opt/mysql4.1.2/data innodb_log_group_home_dir = /opt/mysql4.1.2/var/ innodb_log_arch_dir = /opt/mysql4.1.2/var/ set-variable = innodb_log_file_size=125M set-variable = innodb_log_buffer_size=20M innodb_flush_log_at_trx_commit=1 set-variable = innodb_buffer_pool_size=100M set-variable = innodb_additional_mem_pool_size=10M [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=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy]
left join with multiple OR?
I have some code that's been in production for a few years and now is presenting a problem. The following is on a mysql 3.23.38 server: SELECT b.denewbs, b.dewrbtr, b.dekostl, b.desgtxt, b.deprojk, b.degeber, b.de_heid, c.acdesdc, c.acfund FROM headers a, details b, acctref c WHERE a.he_baid = ? AND b.de_heid = a.heid AND (c.acfund = b.dekostl OR c.acfund = b.deprojk OR c.acfund = b.degeber) ORDER by acfund; Note: The ? is a placeholder for DBI. My problem is this - there is a value that is placed in one, and only one, of dekostl, deprojk, or degeber for each record and this value corresponds to a value in acfund, thus the OR statements. However, some values have recently been removed from acfund, affecting reports where these deleted values are still in existence in the other fields, so I need to add a left join, I suppose. Now in Oracle, I could simply use (+) in all three places. In mysql, I know how to do this with one simple comparison, but not with three. I've tried the following and it didn't like the OR statements. It worked with AND, but of course, there was no acdesc or acfund in the output because it didn't match all three conditions - the acfund is only in one of dekostl, deprojk or degeber - and which one is not known for each record, so I need to check all three. SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid, acdesc, acfund FROM (details INNER JOIN headers ON details.de_heid = headers.heid) LEFT JOIN acctref ON ((details.dekostl = acctref.acfund) OR (details.deprojk = acctref.acfund) OR (details.degeber = acctref.acfund)) WHERE (((headers.he_baid)=38)); at a loss, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RI enforcement and m2m relationships.
[EMAIL PROTECTED] wrote: I don't see how you can possibly hope to satisfy two mutually dependent constraints at the exact same time. It's a chicken-and-egg scenario. If you can't create your vacancy record first (to get its PK value) you won't be able to create the new record in your supervision table. (..) It's not hard to do what you want, but it requires logic external to the storage engine itself to enforce that kind of relationship. In databases this is usually solved in the storage engine itself by deferring constraint checking to transaction commit. Unfortunately, MySQL doesn't offer deferred constraints (haven't seen them on the ToDo list either). Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with totals doubling when using a right join....
Hi All,
I have a problem. I have 2 tables:
mysql SELECT int_traffic.day, sum(int_traffic.deliveries) as deliveries
- FROM int_traffic
- WHERE int_traffic.day between '2004-05-01' and '2004-05-31'
- GROUP BY int_traffic.day
- ORDER BY int_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 6 |
| 2004-05-03 | 40 |
| 2004-05-04 |102 |
| 2004-05-05 |103 |
| 2004-05-06 |119 |
| 2004-05-07 | 57 |
| 2004-05-08 | 17 |
| 2004-05-09 | 12 |
| 2004-05-10 | 87 |
| 2004-05-11 | 82 |
| 2004-05-12 | 86 |
| 2004-05-13 | 76 |
| 2004-05-14 | 68 |
| 2004-05-15 | 38 |
| 2004-05-16 | 9 |
| 2004-05-17 |126 |
| 2004-05-18 | 83 |
| 2004-05-19 |149 |
| 2004-05-20 | 57 |
| 2004-05-21 | 76 |
| 2004-05-22 | 18 |
| 2004-05-23 | 7 |
| 2004-05-24 | 99 |
| 2004-05-25 | 78 |
| 2004-05-26 | 56 |
| 2004-05-27 | 16 |
| 2004-05-28 | 8 |
+++
And:
mysql SELECT daily_traffic.day, sum(daily_traffic.deliveries) as
deliveries
- FROM daily_traffic
- WHERE daily_traffic.day between '2004-05-01' and '2004-05-31'
- GROUP BY daily_traffic.day
- ORDER BY daily_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 101936 |
| 2004-05-03 | 96954 |
| 2004-05-04 | 190871 |
| 2004-05-05 | 192995 |
| 2004-05-06 | 243518 |
| 2004-05-07 | 353032 |
| 2004-05-08 | 197238 |
| 2004-05-09 | 92295 |
| 2004-05-10 | 208991 |
| 2004-05-11 | 201924 |
| 2004-05-12 | 206812 |
| 2004-05-13 | 200308 |
| 2004-05-14 | 184485 |
| 2004-05-15 | 100602 |
| 2004-05-16 | 89567 |
| 2004-05-17 | 202828 |
| 2004-05-18 | 196956 |
| 2004-05-19 | 197561 |
| 2004-05-20 | 197962 |
| 2004-05-21 | 194170 |
| 2004-05-22 | 102406 |
| 2004-05-23 | 86801 |
| 2004-05-24 | 201442 |
| 2004-05-25 | 197852 |
| 2004-05-26 | 191453 |
| 2004-05-27 | 185538 |
| 2004-05-28 | 167304 |
| 2004-05-29 | 95079 |
| 2004-05-30 | 80982 |
| 2004-05-31 | 95863 |
+++
But when I do this:
mysql SELECT distinct daily_traffic.day,
- sum(daily_traffic.deliveries) as external,
sum(yell_int_traffic.deliveries) as deliveries
- FROM daily_traffic
- LEFT JOIN yell_int_traffic
- ON daily_traffic.day = yell_int_traffic.day
- WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
- GROUP by daily_traffic.day;
++--++
| day| external | deliveries |
++--++
| 2004-05-01 | 203872 | 12 |
| 2004-05-03 | 193908 | 80 |
| 2004-05-04 | 954355 |204 |
| 2004-05-05 | 771980 |206 |
| 2004-05-06 | 974072 |238 |
| 2004-05-07 | 1412128 |114 |
| 2004-05-08 | 394476 | 34 |
| 2004-05-09 | 184590 | 24 |
| 2004-05-10 | 417982 |174 |
| 2004-05-11 | 403848 |164 |
| 2004-05-12 | 413624 |172 |
| 2004-05-13 | 400616 |152 |
| 2004-05-14 | 737940 |136 |
| 2004-05-15 | 201204 | 76 |
| 2004-05-16 | 179134 | 18 |
| 2004-05-17 | 811312 |252 |
| 2004-05-18 | 393912 |166 |
| 2004-05-19 | 395122 |298 |
| 2004-05-20 | 791848 |114 |
| 2004-05-21 | 776680 |152 |
| 2004-05-22 | 204812 | 36 |
| 2004-05-23 | 173602 | 14 |
| 2004-05-24 | 402884 |198 |
| 2004-05-25 | 1187112 |156 |
| 2004-05-26 | 382906 |112 |
| 2004-05-27 | 742152 | 32 |
| 2004-05-28 | 334608 | 16 |
| 2004-05-29 |95079 | NULL |
| 2004-05-30 |80982 | NULL |
| 2004-05-31 |95863 | NULL |
++--++
All the results double for the days where there is data in both tables;
e.g. for the 29th, the total goes from 167304 to 334608 in the left
join. I want to see the same result set format but without the doubled
up numbers.
Does anyone have any ideas how to solve this?
Help will be most appreciated...
Cheers,
Andrew
Sql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql-php] mysqlimport error
nikos [EMAIL PROTECTED] wrote: I'm trying mysqlimport --local -d --fields-enclosed-by=| --fields-terminated-by=; - -lines-terminted-by=\n -unikos -p mydb c://temp//programs.txt but i get an Error: File 'c:/temp/programs.txt' not found (Errcode: 2), when using table: programs What I'm doing wrong? RH-9 - APACHE v2 - mysql 3.23.54 Looks like you run mysqlimport on the server host but file is located on Windows box. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Restore from Backup done with Replication features
Dear Friends, Considering what a backup is done with the method following: 1. One master server replicate to one slave server. 2. The master server handle all manipulations and transactions of data (all tables are InnoDB type). 3. The slave server is exclusively used for to make backups. 4. The replication of the slave server is interrupted (stop slave statement). 5. The slave server is correctly shutdown. 6. One copy of the MySQL's data directory is done via operating system commands. 7. The slave server is started. 8. The replication of the slave server is started (start slave statement). In case problems with the master server, where is needed restore data from backup, is done the following: A. One copy is restored to MySQL's data directory in master server and also in slave server. B. The master server is started. C. The slave server is started. D. The replication of the slave server is started. My questions are: - The copy restored (A) will function in both servers? - The restart of the slave's replication (D) must be done with reset slave or start slave statement? - Exist a better method (1-8) to make consistency backups of the transactional databases without interrupt or block the master server and without use the InnoDB Hot Backup tool? All sugestions will be welcome. Thanks in advance, Renato Cramer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join with multiple OR?
Have you tried using the IN operator? SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid, acdesc, acfund FROM (details INNER JOIN headers ON details.de_heid = headers.heid) LEFT JOIN acctref ON acctref.acfund IN (details.dekostl,details.deprojk,details.degeber) WHERE headers.he_baid=38; You could also split this into two steps. This would help you to work around known issues with nested joins (see also: http://bugs.mysql.com/1677 and http://bugs.mysql.com/1591): CREATE TEMPORARY TABLE tmpJoin1 SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid FROM details INNER JOIN headers ON details.de_heid = headers.heid WHERE headers.he_baid=38 SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid, acdesc, acfund FROM tmpJoin1 LEFT JOIN acctref ON acctref.acfund IN (dekostl,deprojk,degeber) Another optimization could be to put your WHERE restriction into your ON clause of the INNER JOIN. (That way the JOIN processing should match up your tables on fewer total rows) SELECT ... FROM ... INNER JOIN headers ON details.de_heid = headers.heid AND headers.he_baid=38 HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael Ragsdale [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: Fax to: 06/02/2004 09:13 Subject: left join with multiple OR? AM I have some code that's been in production for a few years and now is presenting a problem. The following is on a mysql 3.23.38 server: SELECT b.denewbs, b.dewrbtr, b.dekostl, b.desgtxt, b.deprojk, b.degeber, b.de_heid, c.acdesdc, c.acfund FROM headers a, details b, acctref c WHERE a.he_baid = ? AND b.de_heid = a.heid AND (c.acfund = b.dekostl OR c.acfund = b.deprojk OR c.acfund = b.degeber) ORDER by acfund; Note: The ? is a placeholder for DBI. My problem is this - there is a value that is placed in one, and only one, of dekostl, deprojk, or degeber for each record and this value corresponds to a value in acfund, thus the OR statements. However, some values have recently been removed from acfund, affecting reports where these deleted values are still in existence in the other fields, so I need to add a left join, I suppose. Now in Oracle, I could simply use (+) in all three places. In mysql, I know how to do this with one simple comparison, but not with three. I've tried the following and it didn't like the OR statements. It worked with AND, but of course, there was no acdesc or acfund in the output because it didn't match all three conditions - the acfund is only in one of dekostl, deprojk or degeber - and which one is not known for each record, so I need to check all three. SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid, acdesc, acfund FROM (details INNER JOIN headers ON details.de_heid = headers.heid) LEFT JOIN acctref ON ((details.dekostl = acctref.acfund) OR (details.deprojk = acctref.acfund) OR (details.degeber = acctref.acfund)) WHERE (((headers.he_baid)=38)); at a loss, -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]
Username/Password Basics
I've been studying MySQL for a few weeks now and am about ready to publish some databases online. But I'm confused about usernames and passwords. I understand you can create usernames and passwords on three or four different levels, like root, database, etc. As I understand it, localhost is the standard platform on which all the databases rest. I believe all my databases have the same setting - localhost, newbie_user, Newbie Those aren't my real username and password, of course. But I THINK newbie_user and Newbie are the username and password for localhost, and none of my databases have usernames or passwords. So how do I create one? I thought I remembered seeing the password command in phpMyAdmin, but I can't find it now. And if I create a database username and password on top of a localhost username and password, am I going to have to log in with two usernames and passwords? Then again, I could be mistaken about the localhost username and password; it might be that newbie_user and Newbie are a DATABASE username and password shared by all my databases. __ 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: problem with totals doubling when using a right join....
You have to look at the intermediate results of this to understand why your
sums are doubling up:
FROM daily_traffic
LEFT JOIN yell_int_traffic
ON daily_traffic.day = yell_int_traffic.day
WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
I suspect that you are getting twice the number of rows than you expected.
Change your statement so that it runs without the GROUP BY and so that it
doesn't SUM() all of the columns. I'll bet you will not see the data
pattern you thought you had.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
The
Andrew
Braithwaite To: [EMAIL PROTECTED]
[EMAIL PROTECTED]cc: Rob Leney [EMAIL
PROTECTED]
com Fax to:
Subject: problem with totals doubling
when using a right join
06/02/2004 09:26
AM
Hi All,
I have a problem. I have 2 tables:
mysql SELECT int_traffic.day, sum(int_traffic.deliveries) as deliveries
- FROM int_traffic
- WHERE int_traffic.day between '2004-05-01' and '2004-05-31'
- GROUP BY int_traffic.day
- ORDER BY int_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 6 |
| 2004-05-03 | 40 |
| 2004-05-04 |102 |
| 2004-05-05 |103 |
| 2004-05-06 |119 |
| 2004-05-07 | 57 |
| 2004-05-08 | 17 |
| 2004-05-09 | 12 |
| 2004-05-10 | 87 |
| 2004-05-11 | 82 |
| 2004-05-12 | 86 |
| 2004-05-13 | 76 |
| 2004-05-14 | 68 |
| 2004-05-15 | 38 |
| 2004-05-16 | 9 |
| 2004-05-17 |126 |
| 2004-05-18 | 83 |
| 2004-05-19 |149 |
| 2004-05-20 | 57 |
| 2004-05-21 | 76 |
| 2004-05-22 | 18 |
| 2004-05-23 | 7 |
| 2004-05-24 | 99 |
| 2004-05-25 | 78 |
| 2004-05-26 | 56 |
| 2004-05-27 | 16 |
| 2004-05-28 | 8 |
+++
And:
mysql SELECT daily_traffic.day, sum(daily_traffic.deliveries) as
deliveries
- FROM daily_traffic
- WHERE daily_traffic.day between '2004-05-01' and '2004-05-31'
- GROUP BY daily_traffic.day
- ORDER BY daily_traffic.day;
+++
| day| deliveries |
+++
| 2004-05-01 | 101936 |
| 2004-05-03 | 96954 |
| 2004-05-04 | 190871 |
| 2004-05-05 | 192995 |
| 2004-05-06 | 243518 |
| 2004-05-07 | 353032 |
| 2004-05-08 | 197238 |
| 2004-05-09 | 92295 |
| 2004-05-10 | 208991 |
| 2004-05-11 | 201924 |
| 2004-05-12 | 206812 |
| 2004-05-13 | 200308 |
| 2004-05-14 | 184485 |
| 2004-05-15 | 100602 |
| 2004-05-16 | 89567 |
| 2004-05-17 | 202828 |
| 2004-05-18 | 196956 |
| 2004-05-19 | 197561 |
| 2004-05-20 | 197962 |
| 2004-05-21 | 194170 |
| 2004-05-22 | 102406 |
| 2004-05-23 | 86801 |
| 2004-05-24 | 201442 |
| 2004-05-25 | 197852 |
| 2004-05-26 | 191453 |
| 2004-05-27 | 185538 |
| 2004-05-28 | 167304 |
| 2004-05-29 | 95079 |
| 2004-05-30 | 80982 |
| 2004-05-31 | 95863 |
+++
But when I do this:
mysql SELECT distinct daily_traffic.day,
- sum(daily_traffic.deliveries) as external,
sum(yell_int_traffic.deliveries) as deliveries
- FROM daily_traffic
- LEFT JOIN yell_int_traffic
- ON daily_traffic.day = yell_int_traffic.day
- WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
- GROUP by daily_traffic.day;
++--++
| day| external | deliveries |
++--++
| 2004-05-01 | 203872 | 12 |
| 2004-05-03 | 193908 | 80 |
| 2004-05-04 | 954355 |204 |
| 2004-05-05 | 771980 |206 |
| 2004-05-06 | 974072 |
JOINing complication, help please
CONTACT_X_CUSTOMER.ID_ADDRESS CUSTOMER.ID_ADDRESS_SHIPTO CUSTOMER.ID_ADDRESS_MAIN What I would like is to be able to JOIN conditionally based on the absence/presence of reference SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID (thats the simple part, below is what I want, but it doesn't work of course, but the logic is kinda there) IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, LEFT JOIN ADDRESS ON CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) Sorry if I didn't break that up clear like, it was an attempt to make it more clear :) Is there ANY way I could get this to work (on the lastest version of the 3.x mysql) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Username/Password Basics
David, Think of users as the concatenation of the user and the host from which the user is connecting. That's why in your GRANT statement, you will see the 'user'@'host' semantic employed. When you say 'localhost' you're telling MySQL that the user you are specifying is connecting from the local host. So, [EMAIL PROTECTED] is really one user, [EMAIL PROTECTED] is another user, and so on. When MySQL resolves the connection, I believe it first looks at the host for resolution. You do not have to put a user into the system for every location from which you want to connect. Instead, you can use wildcards like: GRANT ON *.* to 'Newbie'@'196.168.168.%' or maybe 'Newbie'@'%.yourdomain.com' Lou - Original Message - From: David Blomstrom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 10:22 AM Subject: Username/Password Basics I've been studying MySQL for a few weeks now and am about ready to publish some databases online. But I'm confused about usernames and passwords. I understand you can create usernames and passwords on three or four different levels, like root, database, etc. As I understand it, localhost is the standard platform on which all the databases rest. I believe all my databases have the same setting - localhost, newbie_user, Newbie Those aren't my real username and password, of course. But I THINK newbie_user and Newbie are the username and password for localhost, and none of my databases have usernames or passwords. So how do I create one? I thought I remembered seeing the password command in phpMyAdmin, but I can't find it now. And if I create a database username and password on top of a localhost username and password, am I going to have to log in with two usernames and passwords? Then again, I could be mistaken about the localhost username and password; it might be that newbie_user and Newbie are a DATABASE username and password shared by all my databases. __ 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: Username/Password Basics
At 7:22 -0700 6/2/04, David Blomstrom wrote: I've been studying MySQL for a few weeks now and am about ready to publish some databases online. But I'm confused about usernames and passwords. I understand you can create usernames and passwords on three or four different levels, like root, database, etc. As I understand it, localhost is the standard platform on which all the databases rest. No. You can assign *privileges* at four levels: global, database, table, column. These are stored in the user, db, tables_priv, and columns_priv tables in the mysql database. Privileges are associated with user accounts. An account is defined as a host + user value combination. These are stored in the Host and User columns of the user table, along with any global privileges the account has. The account password is stored in the Password column of the user table. I believe all my databases have the same setting - localhost, newbie_user, Newbie Those aren't my real username and password, of course. But I THINK newbie_user and Newbie are the username and password for localhost, and none of my databases have usernames or passwords. So how do I create one? Databases don't have passwords. Accounts do. The accounts can be assigned database-level privileges. I thought I remembered seeing the password command in phpMyAdmin, but I can't find it now. Can't help you there. However, I suggest that if you want to know how the MySQL access control system works, you read the relevant sections of the MySQL Reference Manual. I would not try to intuit it from how phpMyAdmin works. http://dev.mysql.com/doc/mysql/en/Privilege_system.html http://dev.mysql.com/doc/mysql/en/User_Account_Management.html And if I create a database username and password on top of a localhost username and password, am I going to have to log in with two usernames and passwords? Then again, I could be mistaken about the localhost username and password; it might be that newbie_user and Newbie are a DATABASE username and password shared by all my databases. It sounds like your conceptual model is backwards. It might serve you best to toss out that model and read through the sections of the manual noted above. -- 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: JOINing complication, help please
Ok, I got a result here, still trying to determine if its correct or not :) LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join with multiple OR?
Shawn, thank you - that did the trick. -Mike At 10:18 AM 6/2/2004, [EMAIL PROTECTED] wrote: Have you tried using the IN operator? SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid, acdesc, acfund FROM (details INNER JOIN headers ON details.de_heid = headers.heid) LEFT JOIN acctref ON acctref.acfund IN (details.dekostl,details.deprojk,details.degeber) WHERE headers.he_baid=38; [] SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid, acdesc, acfund FROM (details INNER JOIN headers ON details.de_heid = headers.heid) LEFT JOIN acctref ON ((details.dekostl = acctref.acfund) OR (details.deprojk = acctref.acfund) OR (details.degeber = acctref.acfund)) WHERE (((headers.he_baid)=38)); at a loss, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOINing complication, help please
Luc, This looks like you want a list of all Customers with Contacts (because you are basing it on the CONTACT_X_CUSTOMER table) and you want to show the Address (if it exists) or the Shipping Address (if it exists) instead of the Address? Am I close? If I want to get one of two or more result choices in a column, I use an IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement, depending on what it is I am choosing between. In your case I think you want to chose which address to use based on their existence, in this case, if it exists, it won't be null: SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address, 'none') as address FROM Customer c INNER JOIN CONTACT_X_CUSTOMER x ON c.ID = x.CUSTOMER_ID LEFT JOIN Address a ON a.ID = x.ID_ADDRESS LEFT JOIN Address s ON s.ID = x.ID_ADDRESS_SHIPTO In this statement, the COALESCE statement will resolve to be the first non-null expression in the list. If there is no Address that matches your _X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased as s will be NULL, Same with ID_ADDRESS and the table aliased as a. If neither address exists the string 'none' is the result. HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Luc Foisy [EMAIL PROTECTED]To: MYSQL-List (E-mail) [EMAIL PROTECTED] -magic.com cc: Fax to: 06/02/2004 10:46 AM Subject: JOINing complication, help please CONTACT_X_CUSTOMER.ID_ADDRESS CUSTOMER.ID_ADDRESS_SHIPTO CUSTOMER.ID_ADDRESS_MAIN What I would like is to be able to JOIN conditionally based on the absence/presence of reference SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID (thats the simple part, below is what I want, but it doesn't work of course, but the logic is kinda there) IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, LEFT JOIN ADDRESS ON CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) Sorry if I didn't break that up clear like, it was an attempt to make it more clear :) Is there ANY way I could get this to work (on the lastest version of the 3.x mysql) -- 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: JOINing complication, help please
Sorry to reply to myslef but I just saw my own typo. Here is a better example statement: SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.address, a.address, 'none') as address FROM Customer c INNER JOIN CONTACT_X_CUSTOMER x ON c.ID = x.CUSTOMER_ID LEFT JOIN Address a ON a.ID = x.ID_ADDRESS LEFT JOIN Address s ON s.ID = x.ID_ADDRESS_SHIPTO (the problem was: the s and the a tables are aliases of the same table so they should have had the same column names. SORRY !!!) [EMAIL PROTECTED] To: Luc Foisy [EMAIL PROTECTED] 06/02/2004 11:31 cc: MYSQL-List (E-mail) [EMAIL PROTECTED] AM Fax to: Subject: Re: JOINing complication, help please Luc, This looks like you want a list of all Customers with Contacts (because you are basing it on the CONTACT_X_CUSTOMER table) and you want to show the Address (if it exists) or the Shipping Address (if it exists) instead of the Address? Am I close? If I want to get one of two or more result choices in a column, I use an IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement, depending on what it is I am choosing between. In your case I think you want to chose which address to use based on their existence, in this case, if it exists, it won't be null: SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address, 'none') as address FROM Customer c INNER JOIN CONTACT_X_CUSTOMER x ON c.ID = x.CUSTOMER_ID LEFT JOIN Address a ON a.ID = x.ID_ADDRESS LEFT JOIN Address s ON s.ID = x.ID_ADDRESS_SHIPTO In this statement, the COALESCE statement will resolve to be the first non-null expression in the list. If there is no Address that matches your _X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased as s will be NULL, Same with ID_ADDRESS and the table aliased as a. If neither address exists the string 'none' is the result. HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Luc Foisy [EMAIL PROTECTED]To: MYSQL-List (E-mail) [EMAIL PROTECTED] -magic.com cc: Fax to: 06/02/2004 10:46 AM Subject: JOINing complication, help please CONTACT_X_CUSTOMER.ID_ADDRESS CUSTOMER.ID_ADDRESS_SHIPTO CUSTOMER.ID_ADDRESS_MAIN What I would like is to be able to JOIN conditionally based on the absence/presence of reference SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID (thats the simple part, below is what I want, but it doesn't work of course, but the logic is kinda there) IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, LEFT JOIN ADDRESS ON CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) Sorry if I didn't break that up clear like, it was an attempt to make it more clear :) Is there ANY way I could get this to work (on the lastest version of the 3.x mysql) -- 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]
Completely Stumped - phpMyAdmin config
Hello: After an all nighter, and reading and trying as much as I can, I'm completely stumped. I'm running RedHat 9, Apache 2.0, SSL, mySQL 3.23, PHP-4.2.2 I can create and insert tables on the server command line. I can run php coded pages residing on the server from a remote browser. I can open phpMyAdmin from a remote browser using http access. I cannot access tables using phpMyAdmin from a remote browser. There is no mention of php in the httpd.conf, but I have php.conf in the http dir. Can anyone please help? Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Completely Stumped - phpMyAdmin config
I remember something setting mine up. The phpMyAdmin.ini (I think) had to have the correct IP address of the machine/database it was running on. My machines internal address was 192.168.2.100, but the INI setting had to be the outside resolved IP 24.16.xx.xx if running it from outside. Hello: After an all nighter, and reading and trying as much as I can, I'm completely stumped. I'm running RedHat 9, Apache 2.0, SSL, mySQL 3.23, PHP-4.2.2 I can create and insert tables on the server command line. I can run php coded pages residing on the server from a remote browser. I can open phpMyAdmin from a remote browser using http access. I cannot access tables using phpMyAdmin from a remote browser. There is no mention of php in the httpd.conf, but I have php.conf in the http dir. Can anyone please help? Ken -- 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: Completely Stumped - phpMyAdmin config
hi Have you changed the auth bits in config.inc.php in the phpMyAdmin directory?? You need to tell phpMyAdmin how and who to connect to mysql as. I can't remember the exact lines but there are 3 lines in the 'servers' section containing (I think) auth_type, user and password. These need to be correct for your setup or mysql will reject the connection HTH Steve kloomis wrote: Hello: After an all nighter, and reading and trying as much as I can, I'm completely stumped. I'm running RedHat 9, Apache 2.0, SSL, mySQL 3.23, PHP-4.2.2 I can create and insert tables on the server command line. I can run php coded pages residing on the server from a remote browser. I can open phpMyAdmin from a remote browser using http access. I cannot access tables using phpMyAdmin from a remote browser. There is no mention of php in the httpd.conf, but I have php.conf in the http dir. Can anyone please help? Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Stats] MySQL List: May 2004
-- Searchable archives for this list are available at http://www.listsearch.com/mysql.lasso -- == MySQL List Stats May, 2004 == Note: Up/Down % as compared with April, 2004 Posts: 1789 (Down 2%) Authors: 528 (Down 3%) Threads: 604 (Down 4%) Top 20 Contributors by Number of Posts -- Egor Egorov 49 Victor Pendleton49 David Blomstrom 44 Paul DuBois 42 Sasha Pachev42 Victoria Reznichenko41 Michael Stassen 40 Jeremy Zawodny 34 SGreen 26 Lou Olsten 23 Jigal van Hemert21 Greg Willits19 Robert J Taylor 19 Daniel Kasak17 Josh Trutwin17 Michael Kruckenberg 17 David Griffiths 16 Dan Nelson 16 Donny Simonton 15 Daniel Clark15 Top 20 Threads by Number of Posts -- BLOB's - General Guidance 26 Simple table, 1.7 million rows, very slow SELECTs 20 fastest filesystem for MySQL19 1-Way or 2-Way Replication? 18 Very Strange data corruption18 urban myth? 17 Foreign Key Constraints 17 InnoDB - Foreign Key - Error 150. 14 FULLTEXT and large database 13 How to dump data in db.table syntax?12 INSERT INTO dropping slashes from strings 11 MySQL does not automatically start at BOOT time?? 11 InnoDB filesystem 11 OPTIMIZE TABLE and mySQL replication11 how do i encrypt the .frm file with a password 10 Search for relationships that aren't present10 Select distinct year from unix timestamp 9 Query question 9 need help with a complicated join9 DB hanging 9 Top 20 Search Terms by Number of Requests -- MySQL 19 to 13 server 12 Can't 11 connect 11 local 10 socket 6 through 6 missing 5 100615 daemon 5 update 4 join 4 Redhat 4 UDF 4 swedish 4 Function 4 tables 4 left
RES: RI enforcement and m2m relationships.
Hello, Maybe a solution to not allow a vacancy to be added without a supervisor being assigned: 1. Create an atribute of the identification in the supervision table as Primary Key (e.g. id_supervision). 2. Create column id_supervision in vacancy table as not null. 3. Create index in vacancy table to column id_supervision. 4. Create foreign key referencing the column id_supervision in vacancy table and supervision table. I hope that helps. Regards, Renato Cramer. -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 2 de junho de 2004 10:02 Para: eifion herbert (IAH-C) Cc: [EMAIL PROTECTED] Assunto: RI enforcement and m2m relationships. Hi all, I'm developing a database to store job adverts to appear on a company website. Each vacancy has at least one supervisor, but may have more than one, and a supervisor may be responsible for multiple vacancies. I've resolved the vacancy--supervisor many-to-many relationship in the normal(?) way of having a table called supervision inbetween them with two columns, the Ref of the vacancy and the ID of the supervisor. Both these columns make up the pri key in the supervision table, and are also foreign keys referencing the pri keys in the vacancy and supervisor table. Unfortunately this allows a vacancy to be added to the database without a supervisor being assigned. Is there a way in mySQL4 or Perl to enforce this without creating an insertion anomaly? Cheers Eifion -- 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]
Unicode characters become question marks
MySQL Server: 4.1.1 alpha MySQL Control Center: 0.9.4 beta I am not sure whether this is a Control Center or MySQL Server problem, but here it goes: I have a database with MyISAM tables created using character set UTF-8. I have installed support for Chinese on my machine, and using MySQL Control Center I entered some values in Chinese for some varchar fields. I had also changed the app font for Control Center to SimSun, which supports Chinese characters. When I requery the table, the Chinese characters have been changed to question marks. I expected, of course, that the Chinese characters would be displayed. I tried this to access the data programatically (using an MFC app and ODBC Connector) and it also shows question marks. I'm not sure whether the conversion to question marks occurs when the data is stored into the table, or when the data is retrieved. I found the following discussion thread debating what seems to be a similar issue, but it was not clear whether to me they ever determined a solution or if it is a bug: http://lists.mysql.com/mysql/164067 Here are the values for my character set variables: +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | character_set_system | utf8 | | character_set_database | utf8 | | character_set_client | utf8 | | character_set_connection | utf8 | | character-sets-dir | C:\mysql\share\charsets/ | | character_set_results| utf8 | +--+--+ I am starting the MySQL server as follows: mysqld --default-character-set=utf8 I need to see the Chinese characters both in Control Center and my MFC app which uses ODBC Connector. Thanks, S Lopes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was Chinese characters in Java; the tables were created with default character set UTF8. Seems only questions marks are stored. Wondering if far-east characters in UTF8 are support by MySQL's UTF8 support? -James From: Silvio Lopes de Oliveira [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unicode characters become question marks Date: Wed, 2 Jun 2004 09:39:14 -0700 MySQL Server: 4.1.1 alpha MySQL Control Center: 0.9.4 beta I am not sure whether this is a Control Center or MySQL Server problem, but here it goes: I have a database with MyISAM tables created using character set UTF-8. I have installed support for Chinese on my machine, and using MySQL Control Center I entered some values in Chinese for some varchar fields. I had also changed the app font for Control Center to SimSun, which supports Chinese characters. When I requery the table, the Chinese characters have been changed to question marks. I expected, of course, that the Chinese characters would be displayed. I tried this to access the data programatically (using an MFC app and ODBC Connector) and it also shows question marks. I'm not sure whether the conversion to question marks occurs when the data is stored into the table, or when the data is retrieved. I found the following discussion thread debating what seems to be a similar issue, but it was not clear whether to me they ever determined a solution or if it is a bug: http://lists.mysql.com/mysql/164067 Here are the values for my character set variables: +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | character_set_system | utf8 | | character_set_database | utf8 | | character_set_client | utf8 | | character_set_connection | utf8 | | character-sets-dir | C:\mysql\share\charsets/ | | character_set_results| utf8 | +--+--+ I am starting the MySQL server as follows: mysqld --default-character-set=utf8 I need to see the Chinese characters both in Control Center and my MFC app which uses ODBC Connector. Thanks, S Lopes -- 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]
Is there any good soul out there who can help me with mysql 4.0.17 replication.
Hi, I am new to mysql and having some trouble with replication. Simple stuff like, 1. How to restart the stopped resplication, 2. How to check if the replication is working, 3. How to bring the slave into master status when master crashes, 4. How to restore master and re-enable replication. Thanks and appreciate very much if some one could donate the time and help. Ravi T. - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to verify that the correct unicode code is being stored. -Original Message- From: James Huang To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 6/2/04 11:45 AM Subject: RE: Unicode characters become question marks I saw the same problem with 5.0 alpha and Java/JDBC. The text was Chinese characters in Java; the tables were created with default character set UTF8. Seems only questions marks are stored. Wondering if far-east characters in UTF8 are support by MySQL's UTF8 support? -James From: Silvio Lopes de Oliveira [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unicode characters become question marks Date: Wed, 2 Jun 2004 09:39:14 -0700 MySQL Server: 4.1.1 alpha MySQL Control Center: 0.9.4 beta I am not sure whether this is a Control Center or MySQL Server problem, but here it goes: I have a database with MyISAM tables created using character set UTF-8. I have installed support for Chinese on my machine, and using MySQL Control Center I entered some values in Chinese for some varchar fields. I had also changed the app font for Control Center to SimSun, which supports Chinese characters. When I requery the table, the Chinese characters have been changed to question marks. I expected, of course, that the Chinese characters would be displayed. I tried this to access the data programatically (using an MFC app and ODBC Connector) and it also shows question marks. I'm not sure whether the conversion to question marks occurs when the data is stored into the table, or when the data is retrieved. I found the following discussion thread debating what seems to be a similar issue, but it was not clear whether to me they ever determined a solution or if it is a bug: http://lists.mysql.com/mysql/164067 Here are the values for my character set variables: +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | character_set_system | utf8 | | character_set_database | utf8 | | character_set_client | utf8 | | character_set_connection | utf8 | | character-sets-dir | C:\mysql\share\charsets/ | | character_set_results| utf8 | +--+--+ I am starting the MySQL server as follows: mysqld --default-character-set=utf8 I need to see the Chinese characters both in Control Center and my MFC app which uses ODBC Connector. Thanks, S Lopes -- 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: Unicode characters become question marks
Yes. The source data were \u escaped in Java, and the data read out of mysql were HTML-escaped into #12345; and set to browser; the browser does display Chinese characters correctly. From: Victor Pendleton [EMAIL PROTECTED] To: 'James Huang ' [EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED] Subject: RE: Unicode characters become question marks Date: Wed, 2 Jun 2004 11:51:22 -0500 Can you display properly handle the Chinese characters? I would try to verify that the correct unicode code is being stored. -Original Message- From: James Huang To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 6/2/04 11:45 AM Subject: RE: Unicode characters become question marks I saw the same problem with 5.0 alpha and Java/JDBC. The text was Chinese characters in Java; the tables were created with default character set UTF8. Seems only questions marks are stored. Wondering if far-east characters in UTF8 are support by MySQL's UTF8 support? -James From: Silvio Lopes de Oliveira [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unicode characters become question marks Date: Wed, 2 Jun 2004 09:39:14 -0700 MySQL Server: 4.1.1 alpha MySQL Control Center: 0.9.4 beta I am not sure whether this is a Control Center or MySQL Server problem, but here it goes: I have a database with MyISAM tables created using character set UTF-8. I have installed support for Chinese on my machine, and using MySQL Control Center I entered some values in Chinese for some varchar fields. I had also changed the app font for Control Center to SimSun, which supports Chinese characters. When I requery the table, the Chinese characters have been changed to question marks. I expected, of course, that the Chinese characters would be displayed. I tried this to access the data programatically (using an MFC app and ODBC Connector) and it also shows question marks. I'm not sure whether the conversion to question marks occurs when the data is stored into the table, or when the data is retrieved. I found the following discussion thread debating what seems to be a similar issue, but it was not clear whether to me they ever determined a solution or if it is a bug: http://lists.mysql.com/mysql/164067 Here are the values for my character set variables: +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | character_set_system | utf8 | | character_set_database | utf8 | | character_set_client | utf8 | | character_set_connection | utf8 | | character-sets-dir | C:\mysql\share\charsets/ | | character_set_results| utf8 | +--+--+ I am starting the MySQL server as follows: mysqld --default-character-set=utf8 I need to see the Chinese characters both in Control Center and my MFC app which uses ODBC Connector. Thanks, S Lopes -- 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]
avoiding Using filesort
i've read the ORDER BY optimization page in the manual but i'm stumped
by my inability to get this query to avoid the filesort.
the table structure is:
CREATE TABLE story (
id int(11) NOT NULL auto_increment,
headline text,
deck text,
free_override tinyint(1) NOT NULL default '0',
output_path varchar(255) NOT NULL default '',
rank smallint(5) unsigned default '0',
has_downloads tinyint(1) NOT NULL default '0',
has_movies tinyint(1) NOT NULL default '0',
has_screens tinyint(1) NOT NULL default '0',
pages mediumint(8) unsigned NOT NULL default '0',
ext_url varchar(255) default '',
post_date datetime default '-00-00 00:00:00',
mod_date datetime default '-00-00 00:00:00',
mod_by varchar(32) default '',
status tinyint(4) NOT NULL default '0',
type tinyint(4) NOT NULL default '0',
subtype tinyint(4) NOT NULL default '0',
mag_id varchar(4) default '',
author varchar(64) default '',
essential_dlx tinyint(4) default '0',
essential tinyint(4) default '0',
graphic varchar(255) default '',
user_level tinyint(4) NOT NULL default '0',
top_slot tinyint(4) default '0',
stylesheet text,
news_industry tinyint(1) NOT NULL default '0',
editor_id int(11) default '0',
PRIMARY KEY (id),
KEY status (status),
KEY editors (editor_id),
KEY ext_url (ext_url),
KEY st (subtype),
KEY post_date (post_date),
KEY type (type),
FULLTEXT KEY jsd (headline,deck)
) TYPE=MyISAM;
the query is:
select id, headline, date_format(s.post_date,'%Y/%m/%d') directory,
post_date sort_date
from story s
where type=3 and status=9
and post_date date_sub(now(),interval 80 day)
and match(headline,deck) against ('halo')
order by post_date desc limit 10
the match() forces filesort to show up in the explain. if i remove it,
i can get rid of the filesort and go back up to 80 days with the
post_date key.
is a fulltext match just doomed to always use filesort? seems odd to
me. i'd think the date restriction would limit the # of rows to be
searched more effectively.
-jsd-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed the application font of MySQL Control Center to SimSun, which supports all the Chinese characters I am using. When I type the characters in MySQL Control Center, I see the Chinese characters. I edit a varchar field in an existing record, I type the Chinese content, hit enter, save the table, and I still see the Chinese characters. When I requery the table, the Chinese characters have become question marks. As for verifying whether the correct Unicode is being stored, how do I do that? All I can see once I requery are the question marks. I don't know whether the Unicode is being stored as question marks, or whether it is stored correctly and is getting converted to question marks when the stored data is retrieved. S Lopes -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 9:51 AM To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] ' Subject: RE: Unicode characters become question marks Can you display properly handle the Chinese characters? I would try to verify that the correct unicode code is being stored. -Original Message- From: James Huang To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 6/2/04 11:45 AM Subject: RE: Unicode characters become question marks I saw the same problem with 5.0 alpha and Java/JDBC. The text was Chinese characters in Java; the tables were created with default character set UTF8. Seems only questions marks are stored. Wondering if far-east characters in UTF8 are support by MySQL's UTF8 support? -James From: Silvio Lopes de Oliveira [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unicode characters become question marks Date: Wed, 2 Jun 2004 09:39:14 -0700 MySQL Server: 4.1.1 alpha MySQL Control Center: 0.9.4 beta I am not sure whether this is a Control Center or MySQL Server problem, but here it goes: I have a database with MyISAM tables created using character set UTF-8. I have installed support for Chinese on my machine, and using MySQL Control Center I entered some values in Chinese for some varchar fields. I had also changed the app font for Control Center to SimSun, which supports Chinese characters. When I requery the table, the Chinese characters have been changed to question marks. I expected, of course, that the Chinese characters would be displayed. I tried this to access the data programatically (using an MFC app and ODBC Connector) and it also shows question marks. I'm not sure whether the conversion to question marks occurs when the data is stored into the table, or when the data is retrieved. I found the following discussion thread debating what seems to be a similar issue, but it was not clear whether to me they ever determined a solution or if it is a bug: http://lists.mysql.com/mysql/164067 Here are the values for my character set variables: +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | character_set_system | utf8 | | character_set_database | utf8 | | character_set_client | utf8 | | character_set_connection | utf8 | | character-sets-dir | C:\mysql\share\charsets/ | | character_set_results| utf8 | +--+--+ I am starting the MySQL server as follows: mysqld --default-character-set=utf8 I need to see the Chinese characters both in Control Center and my MFC app which uses ODBC Connector. Thanks, S Lopes -- 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: Is there any good soul out there who can help me with mysql 4 .0.17 replication.
-Original Message- From: Ravi T To: [EMAIL PROTECTED] Sent: 6/2/04 11:49 AM Subject: Is there any good soul out there who can help me with mysql 4.0.17 replication. Hi, I am new to mysql and having some trouble with replication. Simple stuff like, 1. How to restart the stopped resplication, On the slave: SHOW SLAVE STATUSL Fix the error found in the `Last_error` column 2. How to check if the replication is working, SHOW SLAVE STATUS Both `Slave_IO_Running` and `Slave_SQL_Running` should be `Yes` 3. How to bring the slave into master status when master crashes, The slave needs to be set up to log-bin The change master on the other slaves to point to this new master 4. How to restore master and re-enable replication. Rebuild the tables on the master and sync up the slaves Thanks and appreciate very much if some one could donate the time and help. Ravi T. - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
If you can type the character into the keyboard try this.
SELECT IF(col1=chinese-character, 1, 0)
Else try this from a java program
if (rset.getString(col1).equals(chinese-character))
{
System.out.println(match);
}
else
{System.out.println(invalid);
}
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Sent: 6/2/04 11:59 AM
Subject: RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed
the application font of MySQL Control Center to SimSun, which supports
all the Chinese characters I am using. When I type the characters in
MySQL Control Center, I see the Chinese characters. I edit a varchar
field in an existing record, I type the Chinese content, hit enter, save
the table, and I still see the Chinese characters. When I requery the
table, the Chinese characters have become question marks.
As for verifying whether the correct Unicode is being stored, how do I
do that? All I can see once I requery are the question marks. I don't
know whether the Unicode is being stored as question marks, or whether
it is stored correctly and is getting converted to question marks when
the stored data is retrieved.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 9:51 AM
To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to
verify that the correct unicode code is being stored.
-Original Message-
From: James Huang
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/2/04 11:45 AM
Subject: RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was
Chinese
characters in Java; the tables were created with default character set
UTF8.
Seems only questions marks are stored.
Wondering if far-east characters in UTF8 are support by MySQL's UTF8
support?
-James
From: Silvio Lopes de Oliveira [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Unicode characters become question marks
Date: Wed, 2 Jun 2004 09:39:14 -0700
MySQL Server: 4.1.1 alpha
MySQL Control Center: 0.9.4 beta
I am not sure whether this is a Control Center or MySQL Server problem,
but
here it goes:
I have a database with MyISAM tables created using character set UTF-8.
I
have installed support for Chinese on my machine, and using MySQL
Control
Center I entered some values in Chinese for some varchar fields. I had
also
changed the app font for Control Center to SimSun, which supports
Chinese
characters. When I requery the table, the Chinese characters have been
changed to question marks. I expected, of course, that the Chinese
characters would be displayed.
I tried this to access the data programatically (using an MFC app and
ODBC
Connector) and it also shows question marks. I'm not sure whether the
conversion to question marks occurs when the data is stored into the
table,
or when the data is retrieved.
I found the following discussion thread debating what seems to be a
similar
issue, but it was not clear whether to me they ever determined a
solution
or if it is a bug:
http://lists.mysql.com/mysql/164067
Here are the values for my character set variables:
+--+--+
| Variable_name| Value|
+--+--+
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_set_database | utf8 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character-sets-dir | C:\mysql\share\charsets/ |
| character_set_results| utf8 |
+--+--+
I am starting the MySQL server as follows:
mysqld --default-character-set=utf8
I need to see the Chinese characters both in Control Center and my MFC
app
which uses ODBC Connector.
Thanks,
S Lopes
--
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: Unicode characters become question marks
Victor,
I'm positive the database is storing ?'s. You may test with these steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Thanks,
-James
From: Victor Pendleton [EMAIL PROTECTED]
To: 'Silvio Lopes de Oliveira ' [EMAIL PROTECTED],Victor Pendleton
[EMAIL PROTECTED],'James Huang '
[EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED]
Subject: RE: Unicode characters become question marks
Date: Wed, 2 Jun 2004 12:09:02 -0500
If you can type the character into the keyboard try this.
SELECT IF(col1=chinese-character, 1, 0)
Else try this from a java program
if (rset.getString(col1).equals(chinese-character))
{
System.out.println(match);
}
else
{System.out.println(invalid);
}
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Sent: 6/2/04 11:59 AM
Subject: RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed
the application font of MySQL Control Center to SimSun, which supports
all the Chinese characters I am using. When I type the characters in
MySQL Control Center, I see the Chinese characters. I edit a varchar
field in an existing record, I type the Chinese content, hit enter, save
the table, and I still see the Chinese characters. When I requery the
table, the Chinese characters have become question marks.
As for verifying whether the correct Unicode is being stored, how do I
do that? All I can see once I requery are the question marks. I don't
know whether the Unicode is being stored as question marks, or whether
it is stored correctly and is getting converted to question marks when
the stored data is retrieved.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 9:51 AM
To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to
verify that the correct unicode code is being stored.
-Original Message-
From: James Huang
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/2/04 11:45 AM
Subject: RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was
Chinese
characters in Java; the tables were created with default character set
UTF8.
Seems only questions marks are stored.
Wondering if far-east characters in UTF8 are support by MySQL's UTF8
support?
-James
From: Silvio Lopes de Oliveira [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Unicode characters become question marks
Date: Wed, 2 Jun 2004 09:39:14 -0700
MySQL Server: 4.1.1 alpha
MySQL Control Center: 0.9.4 beta
I am not sure whether this is a Control Center or MySQL Server problem,
but
here it goes:
I have a database with MyISAM tables created using character set UTF-8.
I
have installed support for Chinese on my machine, and using MySQL
Control
Center I entered some values in Chinese for some varchar fields. I had
also
changed the app font for Control Center to SimSun, which supports
Chinese
characters. When I requery the table, the Chinese characters have been
changed to question marks. I expected, of course, that the Chinese
characters would be displayed.
I tried this to access the data programatically (using an MFC app and
ODBC
Connector) and it also shows question marks. I'm not sure whether the
conversion to question marks occurs when the data is stored into the
table,
or when the data is retrieved.
I found the following discussion thread debating what seems to be a
similar
issue, but it was not clear whether to me they ever determined a
solution
or if it is a bug:
http://lists.mysql.com/mysql/164067
Here are the values for my character set variables:
+--+--+
| Variable_name| Value|
+--+--+
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_set_database | utf8 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character-sets-dir | C:\mysql\share\charsets/ |
| character_set_results| utf8 |
+--+--+
I am starting the MySQL server as follows:
mysqld --default-character-set=utf8
I need to see the Chinese characters both in Control Center and my MFC
app
which uses ODBC Connector.
Thanks,
S Lopes
--
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
Initiate perl script in linux from a Windows VBA program
Hi,I would appreciate help with the following. I would like to be ableto initiate a perl script that is residing on a linux server from anExcel workbook. I am working on the Excel workbook in Windows. Iwould appreciate any pointers, sample code for accomplishing this inVBA or some other method.The perl script that I am trying to call will take the informationfrom a mysql database and update the Excel sheet.The script will also take information from the Excel sheet and updatethe database.thanks very much. - Post your free ad now! Yahoo! Canada Personals
RE: Unicode characters become question marks
Okay, I tried the SELECT IF idea, but the results are a little ambiguous. Here is what
I typed:
SELECT IF(networkname=chinese-char, 1, 0) from networktable;
where networkname is a varchar column, and networktable is my table. The result of
this query is 1, which means it matched. However, the header for the result column is
labeled IF(networkname=?, 1, 0). So I can interpret the result of the operation
two ways: 1) the character was stored correctly and the chinese char in my query
matched the chinese char in the db, or 2) the char was stored incorrectly as a '?',
and the char in my query was converted to '?' before the query was executed, in which
case the '?' in my query would match the '?' stored in the db. Either case would
return 1.
Tricky...
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 10:09 AM
To: Silvio Lopes de Oliveira; Victor Pendleton; 'James Huang ';
'[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
If you can type the character into the keyboard try this.
SELECT IF(col1=chinese-character, 1, 0)
Else try this from a java program
if (rset.getString(col1).equals(chinese-character))
{
System.out.println(match);
}
else
{System.out.println(invalid);
}
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Sent: 6/2/04 11:59 AM
Subject: RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed
the application font of MySQL Control Center to SimSun, which supports
all the Chinese characters I am using. When I type the characters in
MySQL Control Center, I see the Chinese characters. I edit a varchar
field in an existing record, I type the Chinese content, hit enter, save
the table, and I still see the Chinese characters. When I requery the
table, the Chinese characters have become question marks.
As for verifying whether the correct Unicode is being stored, how do I
do that? All I can see once I requery are the question marks. I don't
know whether the Unicode is being stored as question marks, or whether
it is stored correctly and is getting converted to question marks when
the stored data is retrieved.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 9:51 AM
To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to
verify that the correct unicode code is being stored.
-Original Message-
From: James Huang
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/2/04 11:45 AM
Subject: RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was
Chinese
characters in Java; the tables were created with default character set
UTF8.
Seems only questions marks are stored.
Wondering if far-east characters in UTF8 are support by MySQL's UTF8
support?
-James
From: Silvio Lopes de Oliveira [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Unicode characters become question marks
Date: Wed, 2 Jun 2004 09:39:14 -0700
MySQL Server: 4.1.1 alpha
MySQL Control Center: 0.9.4 beta
I am not sure whether this is a Control Center or MySQL Server problem,
but
here it goes:
I have a database with MyISAM tables created using character set UTF-8.
I
have installed support for Chinese on my machine, and using MySQL
Control
Center I entered some values in Chinese for some varchar fields. I had
also
changed the app font for Control Center to SimSun, which supports
Chinese
characters. When I requery the table, the Chinese characters have been
changed to question marks. I expected, of course, that the Chinese
characters would be displayed.
I tried this to access the data programatically (using an MFC app and
ODBC
Connector) and it also shows question marks. I'm not sure whether the
conversion to question marks occurs when the data is stored into the
table,
or when the data is retrieved.
I found the following discussion thread debating what seems to be a
similar
issue, but it was not clear whether to me they ever determined a
solution
or if it is a bug:
http://lists.mysql.com/mysql/164067
Here are the values for my character set variables:
+--+--+
| Variable_name| Value|
+--+--+
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_set_database | utf8 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character-sets-dir | C:\mysql\share\charsets/ |
| character_set_results| utf8 |
RE: Unicode characters become question marks
I also have an MFC app which reads data from this table, and displays it as '?'. Using
the debugger and its watch pane, I can see the app is reading '?' from the db. The app
handles strings in Chinese without problems elsewhere in the code, this issue only
comes up when reading from the db.
S Lopes
-Original Message-
From: James Huang [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 10:18 AM
To: [EMAIL PROTECTED]; Silvio Lopes de Oliveira;
[EMAIL PROTECTED]
Subject: RE: Unicode characters become question marks
Victor,
I'm positive the database is storing ?'s. You may test with these steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Thanks,
-James
From: Victor Pendleton [EMAIL PROTECTED]
To: 'Silvio Lopes de Oliveira ' [EMAIL PROTECTED],Victor Pendleton
[EMAIL PROTECTED],'James Huang '
[EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED]
Subject: RE: Unicode characters become question marks
Date: Wed, 2 Jun 2004 12:09:02 -0500
If you can type the character into the keyboard try this.
SELECT IF(col1=chinese-character, 1, 0)
Else try this from a java program
if (rset.getString(col1).equals(chinese-character))
{
System.out.println(match);
}
else
{System.out.println(invalid);
}
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Sent: 6/2/04 11:59 AM
Subject: RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed
the application font of MySQL Control Center to SimSun, which supports
all the Chinese characters I am using. When I type the characters in
MySQL Control Center, I see the Chinese characters. I edit a varchar
field in an existing record, I type the Chinese content, hit enter, save
the table, and I still see the Chinese characters. When I requery the
table, the Chinese characters have become question marks.
As for verifying whether the correct Unicode is being stored, how do I
do that? All I can see once I requery are the question marks. I don't
know whether the Unicode is being stored as question marks, or whether
it is stored correctly and is getting converted to question marks when
the stored data is retrieved.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 9:51 AM
To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to
verify that the correct unicode code is being stored.
-Original Message-
From: James Huang
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/2/04 11:45 AM
Subject: RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was
Chinese
characters in Java; the tables were created with default character set
UTF8.
Seems only questions marks are stored.
Wondering if far-east characters in UTF8 are support by MySQL's UTF8
support?
-James
From: Silvio Lopes de Oliveira [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Unicode characters become question marks
Date: Wed, 2 Jun 2004 09:39:14 -0700
MySQL Server: 4.1.1 alpha
MySQL Control Center: 0.9.4 beta
I am not sure whether this is a Control Center or MySQL Server problem,
but
here it goes:
I have a database with MyISAM tables created using character set UTF-8.
I
have installed support for Chinese on my machine, and using MySQL
Control
Center I entered some values in Chinese for some varchar fields. I had
also
changed the app font for Control Center to SimSun, which supports
Chinese
characters. When I requery the table, the Chinese characters have been
changed to question marks. I expected, of course, that the Chinese
characters would be displayed.
I tried this to access the data programatically (using an MFC app and
ODBC
Connector) and it also shows question marks. I'm not sure whether the
conversion to question marks occurs when the data is stored into the
table,
or when the data is retrieved.
I found the following discussion thread debating what seems to be a
similar
issue, but it was not clear whether to me they ever determined a
solution
or if it is a bug:
http://lists.mysql.com/mysql/164067
Here are the values for my character set variables:
+--+--+
| Variable_name| Value|
+--+--+
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_set_database | utf8 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character-sets-dir | C:\mysql\share\charsets/ |
| character_set_results|
RE: avoiding Using filesort
What does the explain plan look like? It sounds like the query is using the
fulltext index. Remember that only one index per table can be used when
performing a query.
-Original Message-
From: Jon Drukman
To: [EMAIL PROTECTED]
Sent: 6/2/04 11:57 AM
Subject: avoiding Using filesort
i've read the ORDER BY optimization page in the manual but i'm stumped
by my inability to get this query to avoid the filesort.
the table structure is:
CREATE TABLE story (
id int(11) NOT NULL auto_increment,
headline text,
deck text,
free_override tinyint(1) NOT NULL default '0',
output_path varchar(255) NOT NULL default '',
rank smallint(5) unsigned default '0',
has_downloads tinyint(1) NOT NULL default '0',
has_movies tinyint(1) NOT NULL default '0',
has_screens tinyint(1) NOT NULL default '0',
pages mediumint(8) unsigned NOT NULL default '0',
ext_url varchar(255) default '',
post_date datetime default '-00-00 00:00:00',
mod_date datetime default '-00-00 00:00:00',
mod_by varchar(32) default '',
status tinyint(4) NOT NULL default '0',
type tinyint(4) NOT NULL default '0',
subtype tinyint(4) NOT NULL default '0',
mag_id varchar(4) default '',
author varchar(64) default '',
essential_dlx tinyint(4) default '0',
essential tinyint(4) default '0',
graphic varchar(255) default '',
user_level tinyint(4) NOT NULL default '0',
top_slot tinyint(4) default '0',
stylesheet text,
news_industry tinyint(1) NOT NULL default '0',
editor_id int(11) default '0',
PRIMARY KEY (id),
KEY status (status),
KEY editors (editor_id),
KEY ext_url (ext_url),
KEY st (subtype),
KEY post_date (post_date),
KEY type (type),
FULLTEXT KEY jsd (headline,deck)
) TYPE=MyISAM;
the query is:
select id, headline, date_format(s.post_date,'%Y/%m/%d') directory,
post_date sort_date
from story s
where type=3 and status=9
and post_date date_sub(now(),interval 80 day)
and match(headline,deck) against ('halo')
order by post_date desc limit 10
the match() forces filesort to show up in the explain. if i remove it,
i can get rid of the filesort and go back up to 80 days with the
post_date key.
is a fulltext match just doomed to always use filesort? seems odd to
me. i'd think the date restriction would limit the # of rows to be
searched more effectively.
-jsd-
--
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: Initiate perl script in linux from a Windows VBA program
Annie Law wrote: Hi,I would appreciate help with the following. I would like to be ableto initiate a perl script that is residing on a linux server from anExcel workbook. I am working on the Excel workbook in Windows. Iwould appreciate any pointers, sample code for accomplishing this inVBA or some other method.The perl script that I am trying to call will take the informationfrom a mysql database and update the Excel sheet.The script will also take information from the Excel sheet and updatethe database.thanks very much. - Post your free ad now! Yahoo! Canada Personals Why wouldn't you just use ODBC to connect to the server and work directly? Remotely initiating a perl script would involve creating a telnet or SSH session from within Excel using VBA, which would be a far more complex undertaking. -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-380-6535 The Open Source movement has become a major force across the software industry, and MySQL is the world's most popular open source database. --Fortune Magazine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: avoiding Using filesort
Victor Pendleton wrote:
What does the explain plan look like? It sounds like the query is using the
fulltext index. Remember that only one index per table can be used when
performing a query.
explain select id, headline, date_format(s.post_date,'%Y/%m/%d')
directory, post_date sort_date
from story s where type=3 and status=9
and post_date date_sub(now(),interval 80 day)
and match(headline,deck) against ('halo')
order by post_date desc limit 3
+---+--+---++-++--+-+
| table | type | possible_keys | key| key_len | ref
| rows | Extra |
+---+--+---++-++--+-+
| s | fulltext | status,post_date,type,jsd | jsd| 0 |
|1 | Using where; Using filesort |
+---+--+---++-++--+-+
so it's using the fulltext index. can i limit the amount of data it
uses in scanning fulltext so that it won't hit the filesort limit.
also are these:
set-variable=sort_buffer_size=256M
set-variable=myisam_sort_buffer_size=256M
per thread or global? the machine has 6G of RAM in it.
-jsd-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With Remotely Connecting to MySQL Database
I need some help connecting to my database remotely, I would really appreciate any input. Setup: MySQL Server: OS: Linux - Debian MySQL version: 4.0.18 Internal IP: 192.168.1.101 Remote Access PC: OS: Windows XP MySQL: 4.0.20 Internal IP: 192.168.1.100 I am trying to connect from 192.168.1.100 to 192.168.1.101. The computers are connected via a Linksys router. I type the following line from a cmd prompt in Windows XP: mysql -h 192.168.1.101 -u user -p and get the following error: ERROR 2003: Can't connect to MySQL server on '192.168.1.101' (10061) Mysqld is running on the MySQL server. From the server I can localy access the database by typing: mysql -u user at the bash, without any problems. I have done the following for 'user' GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.100' WITH GRANT OPTIONS; This was successfully taken into the user table of the mysql db. (select * from user, shows the entry). I have also tried : GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTIONS; I am not sure where to go from here. I get a feeling there hosts.allow file in /etc/ on the linux machine may have something to do with it. I have addded the following in that file: mysqld : ALL : ALLOW mysqld-max : ALL : ALLOW I did not change the default port of 3306. I assume mysqld automatically opens up the port and begins listening for connections right? Thanks for any help!! Fred Aswad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Username/Password Basics
Lou Olsten wrote, You do not have to put a user into the system for every location from which you want to connect. Instead, you can use wildcards like: GRANT ON *.* to 'Newbie'@'196.168.168.%' or maybe 'Newbie'@'%.yourdomain.com' I don't know how to work with wildcards, but are you describing something I could also do with echo statements? It's a pain in the butt using the same database on two different websites with different usernames and passwords, like david_works and japan_works. For example, one of my webpages begins with the following code: ?php @mysql_connect (localhost , testuser , TestUser); @mysql_select_db (world); Could I replace that with this?: ?php $username = 'webhost_testuser'; $password = 'webhost_pass'; $database = 'world'; @mysql_connect (localhost , ' . $username . ' , ' . $password . '); @mysql_select_db (database); --- Paul DuBois [EMAIL PROTECTED] wrote: At 7:22 -0700 6/2/04, David Blomstrom wrote: I've been studying MySQL for a few weeks now and am about ready to publish some databases online. But I'm confused about usernames and passwords. I understand you can create usernames and passwords on three or four different levels, like root, database, etc. As I understand it, localhost is the standard platform on which all the databases rest. No. You can assign *privileges* at four levels: global, database, table, column. These are stored in the user, db, tables_priv, and columns_priv tables in the mysql database. . . . I thought I remembered seeing the password command in phpMyAdmin, but I can't find it now. Can't help you there. However, I suggest that if you want to know how the MySQL access control system works, you read the relevant sections of the MySQL Reference Manual. I would not try to intuit it from how phpMyAdmin works. http://dev.mysql.com/doc/mysql/en/Privilege_system.html http://dev.mysql.com/doc/mysql/en/User_Account_Management.html H... to make this easier to understand, let me break this down into three sections: 1. What system do I have in operation now? 2. What system SHOULD I have? 3. How do I get there? I put some screen shots from a program called Navicat online at http://www.geoworld.org/userpass.gif In picture #1, you see my eight databases listed on the left. The tables inside database world are listed on the right. When I click Manage Users, I see the following: %localhost [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] I created [EMAIL PROTECTED] and [EMAIL PROTECTED]; one or more of the others are default usernames. Picture 5 illustrates that all eight databases appear inside each user's folder. So users can be thought of as people, and each of these people has access to each database, right? Note the note at the bottom of picture 6: No privileges are currently set for the selected option. So that's what I have. Now, what SHOULD I have? I'm the only person using my computer. I don't envision anyone else working with my databases, unless I create some tables that visitors can add information to using PHP add/edit forms. I'm going to have at least two major databases, one focusing on geography, the other on animals. I'll have others, but these will do as an example. Since I'm presently the only user, which of the following should/could I discard? In other words, let's say I choose minx as a new username, allowing me to discard testuser. And minx gives me access to both the World and Animals databases. Can I discard all the other usernames, or should I retain one or more? %localhost [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] And what global privileges should I use? I think I've answered the third question; it looks like I can add and delete users with Navicat (so I'm going to have to buy it! :) 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: Help With Remotely Connecting to MySQL Database
Is each machine behind a firewall or are both machines behind the same firewall? If the firewalls are different you will need to connect to the firewall address and not the lan address. You will also need to ensure that Port 3306 is open in the firewall. -Original Message- From: Fred Aswad To: [EMAIL PROTECTED] Sent: 6/2/04 12:45 PM Subject: Help With Remotely Connecting to MySQL Database I need some help connecting to my database remotely, I would really appreciate any input. Setup: MySQL Server: OS: Linux - Debian MySQL version: 4.0.18 Internal IP: 192.168.1.101 Remote Access PC: OS: Windows XP MySQL: 4.0.20 Internal IP: 192.168.1.100 I am trying to connect from 192.168.1.100 to 192.168.1.101. The computers are connected via a Linksys router. I type the following line from a cmd prompt in Windows XP: mysql -h 192.168.1.101 -u user -p and get the following error: ERROR 2003: Can't connect to MySQL server on '192.168.1.101' (10061) Mysqld is running on the MySQL server. From the server I can localy access the database by typing: mysql -u user at the bash, without any problems. I have done the following for 'user' GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.100' WITH GRANT OPTIONS; This was successfully taken into the user table of the mysql db. (select * from user, shows the entry). I have also tried : GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTIONS; I am not sure where to go from here. I get a feeling there hosts.allow file in /etc/ on the linux machine may have something to do with it. I have addded the following in that file: mysqld : ALL : ALLOW mysqld-max : ALL : ALLOW I did not change the default port of 3306. I assume mysqld automatically opens up the port and begins listening for connections right? Thanks for any help!! Fred Aswad -- 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]
DaDaBik
After spending days trying to make a PHP form for editing my tables that actually works, I discovered a wonderful script from www.dadabik.org. I haven't used it online yet but I got it up and running on my computer in about five minutes, and it appears to work flawlessly. There's just one catch. If you look at picture #3 at http://www.geoworld.org/userpass.gif you'll see that DaDaBik apparently makes copies of all your tables, thus presumably doubling the file size. I just wondered if anyone has any comments on this, or if you're familiar with other scripts that are comparable or superior. __ 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: Unicode characters become question marks
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
James Huang wrote:
Victor,
I'm positive the database is storing ?'s. You may test with these steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
James,
Have you set your JDBC driver's character set to be UTF-8 using the
characterEncoding property?
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Here's what I get (converting the chars to int to avoid any display
problems)...at least on my end, w/ Connector/J 3.0.14 and MySQL-4.1.x,
what I put in is what I get back out, so my guess is something between
the database and your display is munging the characters...Is whatever
you're using for output set to the correct encoding?:
As Java Unicode (int)chars:
7247
4eee
540d
Retrieved from database as (int)chars:
7247
4eee
540d
(full disclosure, here's my testcase):
public void testFoo() throws Exception {
Properties props = new Properties();
props.setProperty(characterEncoding, utf-8);
Connection utf8Conn = getConnectionWithProps(props);
Statement utf8Stmt = utf8Conn.createStatement();
utf8Stmt.executeUpdate(DROP TABLE IF EXISTS testFoo);
utf8Stmt.executeUpdate(CREATE TABLE testFoo (field1 VARCHAR(32)
CHARACTER SET UTF8) CHARACTER SET UTF8);
utf8Stmt.executeUpdate(INSERT INTO testFoo VALUES
('\u7247\u4EEE\u540D'));
System.out.println(As Java Unicode (int)chars: );
String asUnicode = \u7247\u4EEE\u540D;
for (int i = 0; i asUnicode.length(); i++) {
System.out.println(Integer.toHexString((int)asUnicode.charAt(i)));
}
System.out.println();
ResultSet rs = utf8Stmt.executeQuery(SELECT * FROM testFoo);
rs.next();
String utf8String = rs.getString(1);
System.out.println(Retrieved from database as (int)chars: );
for (int i = 0; i utf8String.length(); i++) {
System.out.println(Integer.toHexString((int)utf8String.charAt(i)));
}
}
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAvha1tvXNTca6JD8RAiB6AJ9FGD0XHFwph8pBJSM5iBQeypbYfwCguIEV
kgjo+ZcICok1bdypNl82cVc=
=uRlQ
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOINing complication, help please
As horible as it looks, this is what I came out with...
SELECT CONTACT_X_CUSTOMER.ID, CONTACT.LastName AS 'Last Name', CONTACT.FirstName AS
'First Name', CONTACT_X_CUSTOMER.Email AS 'Email', CONTACT_X_CUSTOMER.Active AS
'Active', CONCAT(IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0,
CONTACT_X_CUSTOMER_ADDRESS.AddrLine1, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0,
SHIPTO_ADDRESS.AddrLine1, MAIN_ADDRESS.AddrLine1)), IF(CONTACT_X_CUSTOMER.ID_ADDRESS
0, IF(CONTACT_X_CUSTOMER_ADDRESS.AddrLine2 IS NULL OR
TRIM(CONTACT_X_CUSTOMER_ADDRESS.AddrLine2) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_ADDRESS.AddrLine2)), IF(CUSTOMER.ID_ADDRESS_SHIPTO 0,
IF(SHIPTO_ADDRESS.AddrLine2 IS NULL OR TRIM(SHIPTO_ADDRESS.AddrLine2) = '', '',
CONCAT(' ', SHIPTO_ADDRESS.AddrLine2)), IF(MAIN_ADDRESS.AddrLine2 IS NULL OR
TRIM(MAIN_ADDRESS.AddrLine2) = '', '', CONCAT(' ', MAIN_ADDRESS.AddrLine2,
IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, IF(CONTACT_X_CUSTOMER_ADDRESS.AddrLine3 IS NULL
OR TRIM(CONTACT_X_CUSTOMER_ADDRESS.AddrLine3) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_ADDRESS.AddrLine3)), IF(CUSTOMER.ID_ADDRESS_SHIPTO 0,
IF(SHIPTO_ADDRESS.AddrLine3 IS NULL OR TRIM(SHIPTO_ADDRESS.AddrLine3) = '', '',
CONCAT(' ', SHIPTO_ADDRESS.AddrLine3)), IF(MAIN_ADDRESS.AddrLine3 IS NULL OR
TRIM(MAIN_ADDRESS.AddrLine3) = '', '', CONCAT(' ', MAIN_ADDRESS.AddrLine3,
IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, IF(CONTACT_X_CUSTOMER_CITY.Name IS NULL OR
TRIM(CONTACT_X_CUSTOMER_CITY.Name) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_CITY.Name)), IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, IF(SHIPTO_CITY.Name
IS NULL OR TRIM(SHIPTO_CITY.Name) = '', '', CONCAT(' ', SHIPTO_CITY.Name)),
IF(MAIN_CITY.Name IS NULL OR TRIM(MAIN_CITY.Name) = '', '', CONCAT(' ',
MAIN_CITY.Name, IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0,
IF(CONTACT_X_CUSTOMER_PROVINCE.Name IS NULL OR TRIM(CONTACT_X_CUSTOMER_PROVINCE.Name)
= '', '', CONCAT(' ', CONTACT_X_CUSTOMER_PROVINCE.Name)),
IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, IF(SHIPTO_PROVINCE.Name IS NULL OR
TRIM(SHIPTO_PROVINCE.Name) = '', '', CONCAT(' ', SHIPTO_PROVINCE.Name)),
IF(MAIN_PROVINCE.Name IS NULL OR TRIM(MAIN_PROVINCE.Name) = '', '', CONCAT(' ',
MAIN_PROVINCE.Name, IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0,
IF(CONTACT_X_CUSTOMER_COUNTRY.Name IS NULL OR TRIM(CONTACT_X_CUSTOMER_COUNTRY.Name) =
'', '', CONCAT(' ', CONTACT_X_CUSTOMER_COUNTRY.Name)), IF(CUSTOMER.ID_ADDRESS_SHIPTO
0,
IF(SHIPTO_COUNTRY.Name IS NULL OR TRIM(SHIPTO_COUNTRY.Name) = '', '', CONCAT(' ',
SHIPTO_COUNTRY.Name)), IF(MAIN_COUNTRY.Name IS NULL OR TRIM(MAIN_COUNTRY.Name) = '',
'', CONCAT(' ', MAIN_COUNTRY.Name, IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0,
IF(CONTACT_X_CUSTOMER_ADDRESS.PostalCode IS NULL OR
TRIM(CONTACT_X_CUSTOMER_ADDRESS.PostalCode) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_ADDRESS.PostalCode)), IF(CUSTOMER.ID_ADDRESS_SHIPTO 0,
IF(SHIPTO_ADDRESS.PostalCode IS NULL OR TRIM(SHIPTO_ADDRESS.PostalCode) = '', '',
CONCAT(' ', SHIPTO_ADDRESS.PostalCode)), IF(MAIN_ADDRESS.PostalCode IS NULL OR
TRIM(MAIN_ADDRESS.PostalCode) = '', '', CONCAT(' ', MAIN_ADDRESS.PostalCode) AS
'Shipping Address', IFCONTACT_X_CUSTOMER.ID_ADDRESS 0, 'Contact Address',
IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, 'Customer Ship To', 'Customer Main')) AS 'Address
Type' FROM CONTACT_X_CUSTOMER LEFT JOIN CONTACT ON CONTACT_X_CUSTOMER.ID_CONTACT =
CONTACT.ID LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID LEFT
JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0, ONTACT_X_CUSTOMER.ID_ADDRESS =
ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO 0, CUSTOMER.ID_ADDRESS_SHIPTO =
ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) LEFT JOIN ADDRESS
CONTACT_X_CUSTOMER_ADDRESS ON CONTACT_X_CUSTOMER.ID_ADDRESS =
CONTACT_X_CUSTOMER_ADDRESS.ID LEFT JOIN CITY CONTACT_X_CUSTOMER_CITY ON
CONTACT_X_CUSTOMER_ADDRESS.ID_CITY = CONTACT_X_CUSTOMER_CITY.ID LEFT JOIN PROVINCE
CONTACT_X_CUSTOMER_PROVINCE ON CONTACT_X_CUSTOMER_ADDRESS.ID_PROVINCE =
CONTACT_X_CUSTOMER_PROVINCE.ID LEFT JOIN COUNTRY CONTACT_X_CUSTOMER_COUNTRY ON
CONTACT_X_CUSTOMER_ADDRESS.ID_COUNTRY = CONTACT_X_CUSTOMER_COUNTRY.ID LEFT JOIN
ADDRESS SHIPTO_ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = SHIPTO_ADDRESS.ID LEFT JOIN
CITY SHIPTO_CITY ON SHIPTO_ADDRESS.ID_CITY = SHIPTO_CITY.ID LEFT JOIN PROVINCE
SHIPTO_PROVINCE ON SHIPTO_ADDRESS.ID_PROVINCE = SHIPTO_PROVINCE.ID LEFT JOIN COUNTRY
SHIPTO_COUNTRY ON SHIPTO_ADDRESS.ID_COUNTRY = SHIPTO_COUNTRY.ID LEFT JOIN ADDRESS
MAIN_ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = MAIN_ADDRESS.ID LEFT JOIN CITY MAIN_CITY ON
MAIN_ADDRESS.ID_CITY = MAIN_CITY.ID LEFT JOIN PROVINCE MAIN_PROVINCE ON
MAIN_ADDRESS.ID_PROVINCE = MAIN_PROVINCE.ID LEFT JOIN COUNTRY MAIN_COUNTRY ON
MAIN_ADDRESS.ID_COUNTRY = MAIN_COUNTRY.ID WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
ORDER BY CONTACT.LastName
(of course checking for NULL and TRIM() sure adds to the select)
I originally had it looking much better...
LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS 0,
CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID,
Insert .. select problem.
Hello , I'm working on a project with MySql 4.0.12-log. I have a problem with insert .. select: To describe the program of some touristic tours I create two tables: TOUR that contains the data TAPPE that contains the program of the tour. (relation 1:n). To keep track of each tour i create two tables (TOUR_A and TAPPE_A) very closed to the previous two but in tese tables there are also real dates. Now I generate, with PHP, for each tour a set of records in table TOUR_A (1 record a week for a year) and copy the records from TAPPE to TAPPE_A adding dates and the ID of the record of TOUR_A. Test case: 1 record in TOUR 2 records in TAPPE. Generated 141 records in TOUR_A. If I use insert into TAPPE_A select . from TAPPE where TA_ID_TOUR=xx it takes about 25 seconds for 141+141x2 records. If I use select from TAPPE and than INSERT into TAPPE_A in php with a loop it takes less than a second. Does someone have had this problem? It is a bug? The tables have indexes on search fields. Santino Cusimano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode characters become question marks
Thanks, Mark. This instills great confidence in me.
I used this URL:
jdbc:mysql://localhost/mydb?useUnicode=truecharacterEncoding=utf8
(should I use utf-8 perhaps?) Would this work, too?
What is
Connection utf8Conn = getConnectionWithProps(props);
in your test code? That doesn't look like a standard JDBC method.
-James
From: Mark Matthews [EMAIL PROTECTED]
To: James Huang [EMAIL PROTECTED]
CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Unicode characters become question marks
Date: Wed, 02 Jun 2004 13:04:38 -0500
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
James Huang wrote:
Victor,
I'm positive the database is storing ?'s. You may test with these steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
James,
Have you set your JDBC driver's character set to be UTF-8 using the
characterEncoding property?
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Here's what I get (converting the chars to int to avoid any display
problems)...at least on my end, w/ Connector/J 3.0.14 and MySQL-4.1.x,
what I put in is what I get back out, so my guess is something between
the database and your display is munging the characters...Is whatever
you're using for output set to the correct encoding?:
As Java Unicode (int)chars:
7247
4eee
540d
Retrieved from database as (int)chars:
7247
4eee
540d
(full disclosure, here's my testcase):
public void testFoo() throws Exception {
Properties props = new Properties();
props.setProperty(characterEncoding, utf-8);
Connection utf8Conn = getConnectionWithProps(props);
Statement utf8Stmt = utf8Conn.createStatement();
utf8Stmt.executeUpdate(DROP TABLE IF EXISTS testFoo);
utf8Stmt.executeUpdate(CREATE TABLE testFoo (field1 VARCHAR(32)
CHARACTER SET UTF8) CHARACTER SET UTF8);
utf8Stmt.executeUpdate(INSERT INTO testFoo VALUES
('\u7247\u4EEE\u540D'));
System.out.println(As Java Unicode (int)chars: );
String asUnicode = \u7247\u4EEE\u540D;
for (int i = 0; i asUnicode.length(); i++) {
System.out.println(Integer.toHexString((int)asUnicode.charAt(i)));
}
System.out.println();
ResultSet rs = utf8Stmt.executeQuery(SELECT * FROM testFoo);
rs.next();
String utf8String = rs.getString(1);
System.out.println(Retrieved from database as (int)chars: );
for (int i = 0; i utf8String.length(); i++) {
System.out.println(Integer.toHexString((int)utf8String.charAt(i)));
}
}
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAvha1tvXNTca6JD8RAiB6AJ9FGD0XHFwph8pBJSM5iBQeypbYfwCguIEV
kgjo+ZcICok1bdypNl82cVc=
=uRlQ
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOINing complication, help please
Oops, that LEFT JOIN ADDRESS ON IF(etc...) shouldn't be in the big long select statement -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mixing GROUP BY, AVG and COUNT
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 Bring Back the HP 15C http://hp15c.org Not getting the gifts you want? The Wish Zone can help. http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help With Remotely Connecting to MySQL Database
Fred Aswad wrote: I need some help connecting to my database remotely, I would really appreciate any input. Setup: MySQL Server: OS: Linux - Debian MySQL version: 4.0.18 Internal IP: 192.168.1.101 Remote Access PC: OS: Windows XP MySQL: 4.0.20 Internal IP: 192.168.1.100 I am trying to connect from 192.168.1.100 to 192.168.1.101. The computers are connected via a Linksys router. I type the following line from a cmd prompt in Windows XP: mysql -h 192.168.1.101 -u user -p You are asking for a password. and get the following error: ERROR 2003: Can't connect to MySQL server on '192.168.1.101' (10061) Mysqld is running on the MySQL server. From the server I can localy access the database by typing: mysql -u user at the bash, without any problems. I have done the following for 'user' GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.100' WITH GRANT OPTIONS; You forgot to set the password. You forgot identified by 'thepassword' This was successfully taken into the user table of the mysql db. (select * from user, shows the entry). I have also tried : GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTIONS; I am not sure where to go from here. I get a feeling there hosts.allow file in /etc/ on the linux machine may have something to do with it. I have addded the following in that file: mysqld : ALL : ALLOW mysqld-max : ALL : ALLOW I did not change the default port of 3306. I assume mysqld automatically opens up the port and begins listening for connections right? Thanks for any help!! Fred Aswad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
I personally haven't tested with a Java app, but I experience this problem with both
MySQL Control Center and my C++ MFC app using ODBC Connector. Using the Visual C++
debugger, I can inspect the contents of the variables which receive the through the
debugger, and I only see question marks. If I assign Chinese literals to the variables
in my code (rather than reading values from the db), the variable inspection reveals
Chinese characters.
So I would think this has something to do with one of the MySQL character set
settings, but all of them have been set to uft8. I'm running out of ideas.
Does ODBC Connector support the characterEncoding option used with the JDBC driver?
S Lopes
-Original Message-
From: Mark Matthews [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 11:05 AM
To: James Huang
Cc: [EMAIL PROTECTED]; Silvio Lopes de Oliveira;
[EMAIL PROTECTED]
Subject: Re: Unicode characters become question marks
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
James Huang wrote:
Victor,
I'm positive the database is storing ?'s. You may test with these steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
James,
Have you set your JDBC driver's character set to be UTF-8 using the
characterEncoding property?
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Here's what I get (converting the chars to int to avoid any display
problems)...at least on my end, w/ Connector/J 3.0.14 and MySQL-4.1.x,
what I put in is what I get back out, so my guess is something between
the database and your display is munging the characters...Is whatever
you're using for output set to the correct encoding?:
As Java Unicode (int)chars:
7247
4eee
540d
Retrieved from database as (int)chars:
7247
4eee
540d
(full disclosure, here's my testcase):
public void testFoo() throws Exception {
Properties props = new Properties();
props.setProperty(characterEncoding, utf-8);
Connection utf8Conn = getConnectionWithProps(props);
Statement utf8Stmt = utf8Conn.createStatement();
utf8Stmt.executeUpdate(DROP TABLE IF EXISTS testFoo);
utf8Stmt.executeUpdate(CREATE TABLE testFoo (field1 VARCHAR(32)
CHARACTER SET UTF8) CHARACTER SET UTF8);
utf8Stmt.executeUpdate(INSERT INTO testFoo VALUES
('\u7247\u4EEE\u540D'));
System.out.println(As Java Unicode (int)chars: );
String asUnicode = \u7247\u4EEE\u540D;
for (int i = 0; i asUnicode.length(); i++) {
System.out.println(Integer.toHexString((int)asUnicode.charAt(i)));
}
System.out.println();
ResultSet rs = utf8Stmt.executeQuery(SELECT * FROM testFoo);
rs.next();
String utf8String = rs.getString(1);
System.out.println(Retrieved from database as (int)chars: );
for (int i = 0; i utf8String.length(); i++) {
System.out.println(Integer.toHexString((int)utf8String.charAt(i)));
}
}
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAvha1tvXNTca6JD8RAiB6AJ9FGD0XHFwph8pBJSM5iBQeypbYfwCguIEV
kgjo+ZcICok1bdypNl82cVc=
=uRlQ
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DaDaBik
After spending days trying to make a PHP form for editing my tables that actually works, I discovered a wonderful script from www.dadabik.org. I haven't used it online yet but I got it up and running on my computer in about five minutes, and it appears to work flawlessly. There's just one catch. If you look at picture #3 at http://www.geoworld.org/userpass.gif you'll see that DaDaBik apparently makes copies of all your tables, thus presumably doubling the file size. I just wondered if anyone has any comments on this, or if you're familiar with other scripts that are comparable or superior. By the looks of the user interface. It seems that MySQLCC or phpMyAdmin would be comprable/superior options. I use and like both. I've not heard of or used dadabik, but by the screen shots it doesn't seem to be doing anything the other two do not. Since you were leaning the way of PHP, i'd highly reccomend taking at look at phpMyAdmin. http://phpmyadmin.net HTH Jeff __ 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]
Granting privileges to other users
This is probably something simple that I am just not seeing, but I would appreciate your help. As root, I have created a schema named test, along with several tables. I then created a template user named SysAdmin for test using the following syntax. GRANT SELECT, INSERT, UPDATE, DELETE ON TEST TO SysAdmin IDENTIFIED BY 'password' WITH GRANT OPTION; I plan on using SysAdmin as a template for other users. How do I do this? I would expect to be able to login as SysAdmin and then create a new user using something like: GRANT ALL ON TEST TO USER1 IDENTIFIED BY user1'; However I continue to get errors saying something like SysAdmin does not have access to MySQL. Do I have to do this as root? If so, what is the syntax to do so? Thanks. - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
Re: Unicode characters become question marks
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 James Huang wrote: Thanks, Mark. This instills great confidence in me. I used this URL: jdbc:mysql://localhost/mydb?useUnicode=truecharacterEncoding=utf8 (should I use utf-8 perhaps?) Would this work, too? James, Either should work, if you're using the latest version of Connector/J, however 'utf-8' is the 'Java' encoding name, which is more correct. What is Connection utf8Conn = getConnectionWithProps(props); in your test code? That doesn't look like a standard JDBC method. The Junit tests in our testsuite build off a base testcase...getConnectionWithProps(Properties) is a method in this base class that uses the 'standard' URL of our testsuite in combination with the given properties to create a connection. This facilitates having a common URL, but the ability to test cases when various properties (characterEncoding, autoReconnect, etc) affect how the driver behaves. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAvhw+tvXNTca6JD8RAmpuAJsFnIKqZmPhKHBahGidGvRODQSQjwCfYEXs 6RB/NbXSM+UcGLbYfuvAupw= =40ie -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Inverted Index =?iso-8859-1?Q?=BFtable??=
Hi! My name is Nicolas Pace, and i´m quite new in mysql. This is the situation: I have One table with 4 inverted-index fields. I want to query the table which stores the inverted-index keywords. I don´t know if it even exist, but i hope so!! If doesn´t, is there another way to do this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pulling a value from a filed in a result to use as another variable
I am trying to pull cst_SiteID from a query result to use in another query: SELECT cst_Name,cst_SiteID FROM customers WHERE cst_Name LIKE 'st%' +--++ | cst_Name | cst_SiteID | +--++ | Stanley Associates | 8182 | | Starbucks Coffee Holdings| 9261 | | State Farm Mutual Automobile Insurance C | 9523 | | State of New York -NYMTC | 7356 | | State Street Bank and Trust Company | 8305 | | Staubach Company | 8240 | | Staveley Services| 8502 | | Stellar Internet Monitoring, LLC | 8665 | | Stonewall Kitchens | 8221 | | Strategic de Mexico | 4401 | +--++ Can anyone help me figure out how to assign a new variable to the cst_SiteID for each row? Sincerely, Chris Dietzler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Granting privileges to other users
SysAdmin only has rights to the TEST database. This user will need to be able to update the mysql database tables and therefore will need access to the mysql database. -Original Message- From: Robert Frame To: [EMAIL PROTECTED] Sent: 6/2/04 11:15 AM Subject: Granting privileges to other users This is probably something simple that I am just not seeing, but I would appreciate your help. As root, I have created a schema named test, along with several tables. I then created a template user named SysAdmin for test using the following syntax. GRANT SELECT, INSERT, UPDATE, DELETE ON TEST TO SysAdmin IDENTIFIED BY 'password' WITH GRANT OPTION; I plan on using SysAdmin as a template for other users. How do I do this? I would expect to be able to login as SysAdmin and then create a new user using something like: GRANT ALL ON TEST TO USER1 IDENTIFIED BY 'user1'; However I continue to get errors saying something like SysAdmin does not have access to MySQL. Do I have to do this as root? If so, what is the syntax to do so? Thanks. - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting privileges to other users
At 9:15 -0700 6/2/04, Robert Frame wrote: This is probably something simple that I am just not seeing, but I would appreciate your help. As root, I have created a schema named test, along with several tables. I then created a template user named SysAdmin for test using the following syntax. GRANT SELECT, INSERT, UPDATE, DELETE ON TEST What does ON TEST mean? TO SysAdmin IDENTIFIED BY 'password' What does TO SysAdmin mean? That is, what do *you* think the effect of those clauses should be? WITH GRANT OPTION; I plan on using SysAdmin as a template for other users. How do I do this? I would expect to be able to login as SysAdmin and then create a new user using something like: GRANT ALL ON TEST TO USER1 IDENTIFIED BY ëuser1'; However I continue to get errors saying something like SysAdmin does not have access to MySQL. Do I have to do this as root? If so, what is the syntax to do so? -- 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: mixing GROUP BY, AVG and COUNT
How about SELECT @total:=COUNT(*) FROM table; SELECT date, COUNT(*)/@total AS average FROM table GROUP BY date; Michael Chris W wrote: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pulling a value from a filed in a result to use as another variable
Chris Dietzler wrote: I am trying to pull cst_SiteID from a query result to use in another query: SELECT cst_Name,cst_SiteID FROM customers WHERE cst_Name LIKE 'st%' +--++ | cst_Name | cst_SiteID | +--++ | Stanley Associates | 8182 | | Starbucks Coffee Holdings| 9261 | | State Farm Mutual Automobile Insurance C | 9523 | | State of New York -NYMTC | 7356 | | State Street Bank and Trust Company | 8305 | | Staubach Company | 8240 | | Staveley Services| 8502 | | Stellar Internet Monitoring, LLC | 8665 | | Stonewall Kitchens | 8221 | | Strategic de Mexico | 4401 | +--++ Can anyone help me figure out how to assign a new variable to the cst_SiteID for each row? Sincerely, Chris Dietzler Well, if cst_SiteID is autoincrement, try: update customers set cst_SiteID=0 where cst_Name LIKE 'st%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RV: Select data from two different databases
Hi, two questions: Is it possible to select data from different Databases on the same server? Is it possible to select data from different Databases on Different servers across the network? I searched for information on how to do it, but i haven't found any. thanks in advance Alejandro
API Changes from 4.0 to 4.1?
I'm a little out of my realm in asking this on behalf of someone else, but here goes... I finally got 4.1.2 working for me on OS X 10.3.4, but have a curious performance snag. If I connect to MySQL 4.1 via any one of three different GUI Control Center type apps, all works fine. If I connect to 4.1 through Lasso (v6.0.6) middleware, queries are taking a minimum of 5x longer to execute compared to using 4.0.x on that exact same box setup. I have tested with only one MySQL version on the machine at a time. I totally rip one out then install the other. Stock install for each (no my.cnf file). I've repeated this swapping process 2x. There is no difference in speed when using GUI clients like CocoaMySQL, MacSQL, and Navicat, so it's not just 4.1.2 having a general problem. In all cases I am connecting using an old style password for compatibility. Ultimately I'm wondering if there have been changes to the API one would use to build a connector to MySQL with. It sure seems to me that the trouble lies with the Lasso connector, but have there been changes to the MySQL API that could explain that? Both the CocoaMySQL and MacSQL apps I use are old as well, yet they have no problem. BlueWorld doesn't want to bother looking into this because 4.1 is still alpha. So, I'm hoping I can get some indicators here that it is likely the Lasso connector needs updated with some hints as to what to look for at the API level. Maybe this is a question better suited to the PlusPlus list? -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pulling a value from a filed in a result to use as another variable
Actually, cst_SiteID is tied to assets owned by the customer in another table. What I need to do is get the cst_SiteID and use it to get a list of assets from the other table. I have created a successful search page for an individual site id search, but now want to do a search by name, where multiple sites are displayed. Here is my current query: mysql_select_db(mig) or die(Problem selecting database); $query = SELECT * FROM customers WHERE cst_Name LIKE '$_SESSION[Name]%'; $result = mysql_query($query) or die (Query failed); //let's get the number of rows in our result so we can use it in a for loop Search by Site ID SiteID: 7820-- Search for SiteID 7820 + Center Technologies, Inc. 7820-- Results from Search listing Name and Site ID + 37670 7820-www-db0 63.240.145.18 7820-- Assests Associated with SiteID 7820 + -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 11:59 AM To: Chris Dietzler Cc: Mysql (E-mail) Subject: Re: Pulling a value from a filed in a result to use as another variable Chris Dietzler wrote: I am trying to pull cst_SiteID from a query result to use in another query: SELECT cst_Name,cst_SiteID FROM customers WHERE cst_Name LIKE 'st%' +--++ | cst_Name | cst_SiteID | +--++ | Stanley Associates | 8182 | | Starbucks Coffee Holdings| 9261 | | State Farm Mutual Automobile Insurance C | 9523 | | State of New York -NYMTC | 7356 | | State Street Bank and Trust Company | 8305 | | Staubach Company | 8240 | | Staveley Services| 8502 | | Stellar Internet Monitoring, LLC | 8665 | | Stonewall Kitchens | 8221 | | Strategic de Mexico | 4401 | +--++ Can anyone help me figure out how to assign a new variable to the cst_SiteID for each row? Sincerely, Chris Dietzler Well, if cst_SiteID is autoincrement, try: update customers set cst_SiteID=0 where cst_Name LIKE 'st%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RV: Select data from two different databases
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 02 June 2004 01:57 pm, Oropeza Querejeta, Alejandro wrote: Hi, two questions: Is it possible to select data from different Databases on the same server? Select * from db1.table ,db2.table blah blah.. Is it possible to select data from different Databases on Different servers across the network? Have not done this one yet.. - -- %DCL-MEM-BAD, bad memory; VMS-F-PDGERS, pudding between the ears. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAvidfld4MRA3gEwYRAnb/AKCK9KFERC93mpCWAU2y8DqHnWEOhgCeOBLd aU/0ntixFzXSWgq7Dp9RO3o= =4dSu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RV: Select data from two different databases
Yes Local. SELECT databasename.tablename.columnname Hi, two questions: Is it possible to select data from different Databases on the same server? Is it possible to select data from different Databases on Different servers across the network? I searched for information on how to do it, but i haven't found any. thanks in advance Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pulling a value from a filed in a result to use as another variable
I think you need a join. You didn't provide much info about the second table, so I'll pretend it's named assets with columns named host, ip, and cst_SiteID. Something like SELECT c.cst_Name, c.cst_SiteID, a.host, a.ip FROM customers c, assets a WHERE c.cst_SiteID = a.cst_SiteID AND cst_Name LIKE '$_SESSION[Name]%'; should do the trick. Of course, you'll have to replace the assets table and column names with your real values. Michael Chris Dietzler wrote: Actually, cst_SiteID is tied to assets owned by the customer in another table. What I need to do is get the cst_SiteID and use it to get a list of assets from the other table. I have created a successful search page for an individual site id search, but now want to do a search by name, where multiple sites are displayed. Here is my current query: mysql_select_db(mig) or die(Problem selecting database); $query = SELECT * FROM customers WHERE cst_Name LIKE '$_SESSION[Name]%'; $result = mysql_query($query) or die (Query failed); //let's get the number of rows in our result so we can use it in a for loop Search by Site ID SiteID: 7820-- Search for SiteID 7820 + Center Technologies, Inc. 7820-- Results from Search listing Name and Site ID + 37670 7820-www-db0 63.240.145.18 7820-- Assests Associated with SiteID 7820 + -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 11:59 AM To: Chris Dietzler Cc: Mysql (E-mail) Subject: Re: Pulling a value from a filed in a result to use as another variable Chris Dietzler wrote: I am trying to pull cst_SiteID from a query result to use in another query: SELECT cst_Name,cst_SiteID FROM customers WHERE cst_Name LIKE 'st%' +--++ | cst_Name | cst_SiteID | +--++ | Stanley Associates | 8182 | | Starbucks Coffee Holdings| 9261 | | State Farm Mutual Automobile Insurance C | 9523 | | State of New York -NYMTC | 7356 | | State Street Bank and Trust Company | 8305 | | Staubach Company | 8240 | | Staveley Services| 8502 | | Stellar Internet Monitoring, LLC | 8665 | | Stonewall Kitchens | 8221 | | Strategic de Mexico | 4401 | +--++ Can anyone help me figure out how to assign a new variable to the cst_SiteID for each row? Sincerely, Chris Dietzler Well, if cst_SiteID is autoincrement, try: update customers set cst_SiteID=0 where cst_Name LIKE 'st%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode characters become question marks
You know, now I'm sure that the chars are getting stored as '?' as well. I tried the
test you suggested again, but with a small modification. I typed:
SELECT IF(networkname='?', 1, 0) from networktable;
and it returned 1. Because I used '?' instead of the chinese char and it matched, then
obviously the stored character is a '?'. So my conclusion is the same as James
Huang's; the problem happens when the string is stored. But no solution yet, though.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 10:09 AM
To: Silvio Lopes de Oliveira; Victor Pendleton; 'James Huang ';
'[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
If you can type the character into the keyboard try this.
SELECT IF(col1=chinese-character, 1, 0)
Else try this from a java program
if (rset.getString(col1).equals(chinese-character))
{
System.out.println(match);
}
else
{System.out.println(invalid);
}
-Original Message-
From: Silvio Lopes de Oliveira
To: Victor Pendleton; James Huang ; [EMAIL PROTECTED]
Sent: 6/2/04 11:59 AM
Subject: RE: Unicode characters become question marks
Yes, my display can handle the Chinese characters. I have also changed
the application font of MySQL Control Center to SimSun, which supports
all the Chinese characters I am using. When I type the characters in
MySQL Control Center, I see the Chinese characters. I edit a varchar
field in an existing record, I type the Chinese content, hit enter, save
the table, and I still see the Chinese characters. When I requery the
table, the Chinese characters have become question marks.
As for verifying whether the correct Unicode is being stored, how do I
do that? All I can see once I requery are the question marks. I don't
know whether the Unicode is being stored as question marks, or whether
it is stored correctly and is getting converted to question marks when
the stored data is retrieved.
S Lopes
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 9:51 AM
To: 'James Huang '; Silvio Lopes de Oliveira; '[EMAIL PROTECTED] '
Subject: RE: Unicode characters become question marks
Can you display properly handle the Chinese characters? I would try to
verify that the correct unicode code is being stored.
-Original Message-
From: James Huang
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/2/04 11:45 AM
Subject: RE: Unicode characters become question marks
I saw the same problem with 5.0 alpha and Java/JDBC. The text was
Chinese
characters in Java; the tables were created with default character set
UTF8.
Seems only questions marks are stored.
Wondering if far-east characters in UTF8 are support by MySQL's UTF8
support?
-James
From: Silvio Lopes de Oliveira [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Unicode characters become question marks
Date: Wed, 2 Jun 2004 09:39:14 -0700
MySQL Server: 4.1.1 alpha
MySQL Control Center: 0.9.4 beta
I am not sure whether this is a Control Center or MySQL Server problem,
but
here it goes:
I have a database with MyISAM tables created using character set UTF-8.
I
have installed support for Chinese on my machine, and using MySQL
Control
Center I entered some values in Chinese for some varchar fields. I had
also
changed the app font for Control Center to SimSun, which supports
Chinese
characters. When I requery the table, the Chinese characters have been
changed to question marks. I expected, of course, that the Chinese
characters would be displayed.
I tried this to access the data programatically (using an MFC app and
ODBC
Connector) and it also shows question marks. I'm not sure whether the
conversion to question marks occurs when the data is stored into the
table,
or when the data is retrieved.
I found the following discussion thread debating what seems to be a
similar
issue, but it was not clear whether to me they ever determined a
solution
or if it is a bug:
http://lists.mysql.com/mysql/164067
Here are the values for my character set variables:
+--+--+
| Variable_name| Value|
+--+--+
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_set_database | utf8 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character-sets-dir | C:\mysql\share\charsets/ |
| character_set_results| utf8 |
+--+--+
I am starting the MySQL server as follows:
mysqld --default-character-set=utf8
I need to see the Chinese characters both in Control Center and my MFC
app
which uses ODBC Connector.
Thanks,
S Lopes
--
MySQL General Mailing List
For list archives:
RE: Unicode characters become question marks
You know, now I'm sure that the chars are getting stored as '?' as well. I tried the test you suggested again, but with a small modification. I typed: SELECT IF(networkname='?', 1, 0) from networktable; and it returned 1. Because I used '?' instead of the chinese char and it matched, then obviously the stored character is a '?'. So my conclusion is the same as James Huang's; the problem happens when the string is stored. But no solution yet, though. The first thing to be sure to do is execute this query from the client: SET CHARACTER SET utf8; The best way to see what is actually being stored is to select the hex value of the column: SELECT HEX(your_column) FROM your_table; To see hex values as unicode codepoints convert the utf8 to ucs2: SELECT HEX(CONVERT(your_column USING ucs2)) FROM your_table; I'm not sure if this is equivalent to the example given in java before or not, but this is how I always insert hex values directly: INSERT INTO your_table VALUES (CONVERT(_ucs2 0x1234 USING utf8)); where '1234' is a unicode codepoint. This way you can enter the character as the codepoint and convert it to the utf8 equivalent. FYI I'm fairly sure that MyODBC will not support unicode until version 3.52. When I tried to use MyODBC for unicode a while back all I got was ???. You might try upgrading to 4.1.2--it has better support for character set conversions and a new ucs2_general_uca collation which uses the Unicode Collation Algorithm. Another thing to consider is that MySQL only supports utf8 characters up to 3 bytes long. I don't know if this is the case for chinese or not, but if so that might be another reason to use ucs2. good luck, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where do exported SQL files go?
I'm trying to figure out how to get my MySQL databases online. Someone told me to EXPORT my databases as SQL files, then go into my online websites's phpMyAdmin program and import it, and everything else would fall into place. At first, I was confused and exported just a single table. I was able to import it online. Then I went back and exported my entire database, but I couldn't import it, because I couldn't find it. I exported it again under a distinctive name - zorex - then did a Windows search for it. No trace. The table I saved was on my desktop, but there's no trace of a database SQL on my desktop, nor have I found it in my MySQL or phpMyAdmin folders. Any tips? __ 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: Unicode characters become question marks
I tried SELECT HEX(your_column) FROM your_table and indeed, only '?' is being stored (3F hex, 63 decimal). Thanks for the hint regarding Unicode support in MyODBC, I'll try to read more on it tomorrow. I've had enough frustrations for the day... :) Thanks. S Lopes -Original Message- From: Jeremy March [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 02, 2004 2:53 PM To: [EMAIL PROTECTED] Cc: Silvio Lopes de Oliveira Subject: RE: Unicode characters become question marks You know, now I'm sure that the chars are getting stored as '?' as well. I tried the test you suggested again, but with a small modification. I typed: SELECT IF(networkname='?', 1, 0) from networktable; and it returned 1. Because I used '?' instead of the chinese char and it matched, then obviously the stored character is a '?'. So my conclusion is the same as James Huang's; the problem happens when the string is stored. But no solution yet, though. The first thing to be sure to do is execute this query from the client: SET CHARACTER SET utf8; The best way to see what is actually being stored is to select the hex value of the column: SELECT HEX(your_column) FROM your_table; To see hex values as unicode codepoints convert the utf8 to ucs2: SELECT HEX(CONVERT(your_column USING ucs2)) FROM your_table; I'm not sure if this is equivalent to the example given in java before or not, but this is how I always insert hex values directly: INSERT INTO your_table VALUES (CONVERT(_ucs2 0x1234 USING utf8)); where '1234' is a unicode codepoint. This way you can enter the character as the codepoint and convert it to the utf8 equivalent. FYI I'm fairly sure that MyODBC will not support unicode until version 3.52. When I tried to use MyODBC for unicode a while back all I got was ???. You might try upgrading to 4.1.2--it has better support for character set conversions and a new ucs2_general_uca collation which uses the Unicode Collation Algorithm. Another thing to consider is that MySQL only supports utf8 characters up to 3 bytes long. I don't know if this is the case for chinese or not, but if so that might be another reason to use ucs2. good luck, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting mysql working
Hi, I am trying to get MsSQL working. I can't get the server to start. I tried typing in mysql_install_db and got the following response: linux:/home/nick # mysql_install_db Installing all prepared tables 040602 5:54:02 /usr/sbin/mysqld: Shutdown Complete PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h linux password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! But this did not start it. I tried typing in rcmysql start but that failed. I can't get the service started in the first place so I can't add any passwards to any accounts. I installed the mysql with the suse 9.1 pro cd. I am thinking that I should reinstall my mysql. But I don't know how to do that. Can someone please tell me how to fix this or how to reinstall it? Nick _ Check out the coupons and bargains on MSN Offers! http://youroffers.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: getting mysql working
Have you tried this: bin/safe_mysqld --user=mysql ? Or there is a script inside supporting called mysql.server, you can start it up by typing mysql.server start Hope this help! Best Regards, Jonathan Chiu OOCL Logistics Unit 1, 4/F., Sun Hung Kai Centre, 30 Harbour Road, Wanchai TEL: 852 . 2990 0174 FAX: 852 . 28249017 -Original Message- From: Nick Mudge [mailto:[EMAIL PROTECTED] Sent: Thursday, June 03, 2004 9:22 AM To: [EMAIL PROTECTED] Subject: getting mysql working Hi, I am trying to get MsSQL working. I can't get the server to start. I tried typing in mysql_install_db and got the following response: linux:/home/nick # mysql_install_db Installing all prepared tables 040602 5:54:02 /usr/sbin/mysqld: Shutdown Complete PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h linux password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! But this did not start it. I tried typing in rcmysql start but that failed. I can't get the service started in the first place so I can't add any passwards to any accounts. I installed the mysql with the suse 9.1 pro cd. I am thinking that I should reinstall my mysql. But I don't know how to do that. Can someone please tell me how to fix this or how to reinstall it? Nick _ Check out the coupons and bargains on MSN Offers! http://youroffers.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] IMPORTANT NOTICE Email from OOCL is confidential and may be legally privileged. If it is not intended for you, please delete it immediately unread. The internet cannot guarantee that this communication is free of viruses, interception or interference and anyone who communicates with us by email is taken to accept the risks in so doing. Without limitation, OOCL and its affiliates accept no liability whatsoever and howsoever arising in connection with the use of this email. Under no circumstances shall this email constitute a binding agreement to carry or for provision of carriage services by OOCL, which is subject to the availability of carrier's equipment and vessels and the terms and conditions of OOCL's standard bill of lading which is also available at http://www.oocl.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Codes for U.S. Counties
I have a big database focusing on U.S. counties. I'd like to assign each county a code for use as a key. I'm thinking of simply using the state postal code, followed by numerals. For example, Arizona's counties would be arranged alphabetically, beginning with az1, az2, az3, etc. I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. 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: Where do exported SQL files go?
Using phpmyadmin, when I select Export, and click the checkbox Save as File, it prompts where for where and what file name. I'm trying to figure out how to get my MySQL databases online. Someone told me to EXPORT my databases as SQL files, then go into my online websites's phpMyAdmin program and import it, and everything else would fall into place. At first, I was confused and exported just a single table. I was able to import it online. Then I went back and exported my entire database, but I couldn't import it, because I couldn't find it. I exported it again under a distinctive name - zorex - then did a Windows search for it. No trace. The table I saved was on my desktop, but there's no trace of a database SQL on my desktop, nor have I found it in my MySQL or phpMyAdmin folders. Any tips? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where do exported SQL files go?
--- Daniel Clark [EMAIL PROTECTED] wrote: Using phpmyadmin, when I select Export, and click the checkbox Save as File, it prompts where for where and what file name. Aha - you nailed it. I wasn't checking the Save As box because the SQL box above was already checked. I tried it again, checking Save As, and it saved to my desktop. (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) 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]
Exporting/Importing Databases
I just got my first MySQL database online, but I have some questions. First, if I want to import a database named, say username_jack, do I have to create a database named username_jack online first? In other words, could I access an online MySQL account that's empty, with no databases, then just import username_jack? Second, after I've published a database online, if I modify the database on my local computer, can I just republish it online on top of whatever's already online? Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? Ironically, counties comes after continents alphabetically, so it nixed all the tables that followed. __ 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]
Self-Join Query
Hi All, I'm attempting to generate a report of page hits from both internal and external IP addresses, from one table, using self join. Some sample data: ++-+++ | hit_no | page_name | ip | hit_time | ++-+++ | 6649 | printing| 10.77.1.128| 1061362239 | | 6650 | wireless| 10.77.28.100 | 1061365331 | | 6651 | after_hours | 10.77.31.101 | 1061365461 | | 6632 | labtimes| 10.77.25.102 | 1061350012 | | 6633 | after_hours | 10.77.25.102 | 1061350017 | | 6634 | labtimes| 10.77.25.102 | 1061350325 | | 6635 | practise| 10.77.30.114 | 1061350609 | | 6636 | support | 10.19.7.155| 1061352345 | | 6637 | help| 203.35.134.16 | 1061352351 | | 6638 | support | 10.19.7.156| 1061352352 | | 6639 | support | 10.19.7.151| 1061352387 | | 6640 | support | 10.19.7.159| 1061352411 | | 6621 | support | 10.19.7.158| 1061348961 | | 6620 | support | 10.19.6.112| 1061348628 | | 7318 | labtimes| 202.137.192.7 | 1063262879 | | 6284 | conditions | 10.77.31.109 | 1060605402 | | 7317 | practise| 202.137.192.7 | 1063262789 | | 7316 | wireless| 203.59.185.185 | 1063262707 | | 7315 | wireless| 10.77.28.121 | 1063256685 | ++-+++ I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 |2 | | help| 458 | 174 | | labtimes| 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise| 497 | 99 | | printing| 801 | 85 | | wireless| 926 | 180 | +-+--+--+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; The query takes between 30 seconds and 4.5 minutes to process 6000 rows, depending upon what extra WHERE conditions I put in (like i.hit_no = e.hit_no, or i.page_name = e.page_name etc), the result of which looks like: +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 638520 | 638520 | | conditions | 353064 | 353064 | | faq | 108924 | 108924 | | help| 355568 | 355568 | | labtimes| 674828 | 674828 | | oracle | 2213536 | 2213536 | | passwords | 165264 | 165264 | | practise| 498296 | 498296 | | printing| 896432 | 896432 | | wireless| 933992 | 933992 | +-+--+--+ Can anyone offer any suggestions as to the problem with my logic? Regards, James Katarski Systems Administrator School of Computer Information Science Edith Cowan University ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting mysql working
At 1:22 + 6/3/04, Nick Mudge wrote: Hi, I am trying to get MsSQL working. I can't get the server to start. I tried typing in mysql_install_db and got the following response: linux:/home/nick # mysql_install_db Installing all prepared tables 040602 5:54:02 /usr/sbin/mysqld: Shutdown Complete PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h linux password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! But this did not start it. I tried typing in rcmysql start but that failed. By this did not start it, what do you mean by this? mysql_install_db? That's not supposed to start the server. It starts the server only to install the grant tables, then the server shuts down, as you see above. What part of the manual are you reading to find out how to start the server? I recommend this section: http://dev.mysql.com/doc/mysql/en/Automatic_start.html I don't know what rcmysql is, but if you provide more information than that failed you might get some assistance from those who do know what it is. For example, if you get an error message, please indicate what it is. I can't get the service started in the first place so I can't add any passwards to any accounts. I installed the mysql with the suse 9.1 pro cd. I am thinking that I should reinstall my mysql. But I don't know how to do that. Can someone please tell me how to fix this or how to reinstall it? -- 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: RV: Select data from two different databases
At 13:57 -0500 6/2/04, Oropeza Querejeta, Alejandro wrote: Hi, two questions: Is it possible to select data from different Databases on the same server? Yes. You can qualify table and column names with the database name to specify which database you mean. http://dev.mysql.com/doc/mysql/en/Identifier_qualifiers.html Is it possible to select data from different Databases on Different servers across the network? Not within the same connection. Depending on your API, you may be able to open two connections within your program, but joining the data selected from the two connections is something you have to take care of. Ugly. -- 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: Short Passwords in 4.1.2
At 21:12 -0700 6/1/04, Greg Willits wrote: I'm trying to force 4.1.2 to use the old short passwords for now during some experimental stages. The discussion here (specifically the fourth set of bullets): http://dev.mysql.com/doc/mysql/en/Password_hashing.html and, this paragraph: The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to widen the Password column. led me to think that I could modify the user table and set the width of the Password column to varchar(16) and based on the narrow column 4.1.2 would always default to creating the old passwords. After narrowing the column, did you leave the server running? Or did you restart it? However, GRANT statements are creating passwords that start with * so, even though they're chopped off at 16, they're obviously still the new format. - Sidebar: interesting--mysql 4.0 creates host, user, password columns as binary but 4.1.2 does not? - Can 4.1.2 be forced to used old passwords this way? Do I need to modify the startup script to include --old-passwords? -- greg willits -- 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: Codes for U.S. Counties
David Blomstrom wrote: I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. I don't know, but you might ask the folks at: http://www.naco.org/ The National Association of Counties (NACo) =Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE error
At 5:18 -0500 6/2/04, [EMAIL PROTECTED] wrote:
There's got ot be something else wrong. I've got the exact same problem and
I have done a chmod 666 on the directory I am trying to write to with no
luck. I am running v4.0.15
Mode is not enough, you must consider ownership.
Remember, *you* are not trying to write the file. The MySQL server
(mysqld) is trying to write the file, and it might not be running
under your user ID. It's probably running from the mysql account.
/T
on 6/1/04 7:06, Egor Egorov at [EMAIL PROTECTED] wrote:
John Mistler [EMAIL PROTECTED] wrote:
Would there be any obvious reasons for its inability to access the desktop?
I have used that desktop directory many times before in UNIX.
Check permissions on that dir. It must be writeable by the user
you run mysqld
as ('mysql' by default).
on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote:
John Mistler [EMAIL PROTECTED] wrote:
Can anyone guess why I am getting this error:
ERROR 1: Can't create/write to file
'/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13)
when using this statement:
mysql SELECT * INTO OUTFILE
'/Users/johnmistler/Desktop/TestFile.txt' FROM
theTable WHERE column1 != 'thisString';
? I am logging in as root for the test.
It means that MySQL server can not access to the
/Users/johnmistler/Desktop/
directory.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
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: Sub query on mySQL 4.0.18
Jigal van Hemert wrote: I don#t understand why subquery on my MySQL 4.0.18-Mas does not valid. select * from salesreport where custid in (select custid from appointment where done='N'); Simple reason: v. 4.0.18 does not support subqueries yet. Use 4.1 or later. Regards, Jigal. Or, rewrite the query as a join. SELECT * FROM salesreport s, appointment a WHERE s.custid = a.custid AND a.done = 'N'; See http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Codes for U.S. Counties
--- P. Hill [EMAIL PROTECTED] wrote: David Blomstrom wrote: I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. I don't know, but you might ask the folks at: http://www.naco.org/ The National Association of Counties (NACo) I checked NACO out long ago and decided they're an amateur act. My project is nicer than theirs (though mine isn't online yet). :) __ 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: Exporting/Importing Databases
At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases: Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
--- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases: Third, my practice database includes half a dozen tables, but only one made it online, followed by this error message: Database USERNAME running on localhost Error SQL-query : CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB AUTO_INCREMENT =3143 MySQL said: #1005 - Can't create table './[USERNAME]/counties.frm' (errno: 150) Do you know what this means? You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist. The fix is to temporally turn off RI during the load. Put this command at the start of your file: SET FOREIGN_KEY_CHECKS = 0; and this one at the end: SET FOREIGN_KEY_CHECKS = 1; and it will work. OK, is this something I can do in phpMyAdmin or another software program, or do I have to open the MySQL file itself? I've scarcely touched MySQL files, but I found the file counties.frm, which I assume I would open with Notepad, right? And then I can make SET FOREIGN_KEY_CHECKS = 0; the very first line and SET FOREIGN_KEY_CHECKS = 1; the very last line, after which I would EXPORT my database, then import it, right? And since I like to plan ahead, is it OK to insert these two lines in ALL my MySQL documents, just to be prepared for this error? If I inadvertently stick these lines in a file that doesn't have a foreign key, will it cause a problem? Finally, after I've imported my database online, do I have to go back and remove these two lines, or can I just leave them there indefinitely as a safeguard? 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: Where do exported SQL files go?
At 20:13 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) Might it be doing this due to PHPAdmin being a WebPage application and thus being served by your Web Server making the file need to be a FTP Download g? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode characters become question marks
This test code works, with both mysql-connector-j 3.0.14 and 3.0.11, against
mysql 5.0-alpha. I'll look more and report anything if interesting.
Thanks,
-James
From: Mark Matthews [EMAIL PROTECTED]
To: James Huang [EMAIL PROTECTED]
CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Unicode characters become question marks
Date: Wed, 02 Jun 2004 13:04:38 -0500
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
James Huang wrote:
Victor,
I'm positive the database is storing ?'s. You may test with these steps:
1) insert \u7247\u4EEE\u540D into a UTF8 table;
James,
Have you set your JDBC driver's character set to be UTF-8 using the
characterEncoding property?
2) Query and get it back into string s;
3) for each char c in s: System.out.println((int)c);
Here's what I get (converting the chars to int to avoid any display
problems)...at least on my end, w/ Connector/J 3.0.14 and MySQL-4.1.x,
what I put in is what I get back out, so my guess is something between
the database and your display is munging the characters...Is whatever
you're using for output set to the correct encoding?:
As Java Unicode (int)chars:
7247
4eee
540d
Retrieved from database as (int)chars:
7247
4eee
540d
(full disclosure, here's my testcase):
public void testFoo() throws Exception {
Properties props = new Properties();
props.setProperty(characterEncoding, utf-8);
Connection utf8Conn = getConnectionWithProps(props);
Statement utf8Stmt = utf8Conn.createStatement();
utf8Stmt.executeUpdate(DROP TABLE IF EXISTS testFoo);
utf8Stmt.executeUpdate(CREATE TABLE testFoo (field1 VARCHAR(32)
CHARACTER SET UTF8) CHARACTER SET UTF8);
utf8Stmt.executeUpdate(INSERT INTO testFoo VALUES
('\u7247\u4EEE\u540D'));
System.out.println(As Java Unicode (int)chars: );
String asUnicode = \u7247\u4EEE\u540D;
for (int i = 0; i asUnicode.length(); i++) {
System.out.println(Integer.toHexString((int)asUnicode.charAt(i)));
}
System.out.println();
ResultSet rs = utf8Stmt.executeQuery(SELECT * FROM testFoo);
rs.next();
String utf8String = rs.getString(1);
System.out.println(Retrieved from database as (int)chars: );
for (int i = 0; i utf8String.length(); i++) {
System.out.println(Integer.toHexString((int)utf8String.charAt(i)));
}
}
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAvha1tvXNTca6JD8RAiB6AJ9FGD0XHFwph8pBJSM5iBQeypbYfwCguIEV
kgjo+ZcICok1bdypNl82cVc=
=uRlQ
-END PGP SIGNATURE-
--
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: Where do exported SQL files go?
--- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:13 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) Might it be doing this due to PHPAdmin being a WebPage application and thus being served by your Web Server making the file need to be a FTP Download g? Well, that's as good an explanation as any. :) It isn't that big a deal; at least I know where I can find everything I download, and I can always copy and rename them and move them somewhere else if necessary. It just confused me this time around. __ 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: Where do exported SQL files go?
You can change the download options so that it will ask you where you want to put the file rather than default to the desktop. Tools/Options/Downloads/Ask me where to put every file. Dave -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Thursday, June 03, 2004 12:18 AM To: [EMAIL PROTECTED] Subject: Re: Where do exported SQL files go? --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: At 20:13 -0700 on 06/02/2004, David Blomstrom wrote about Re: Where do exported SQL files go?: (I'm using Mozilla Firefox, which downloads everything to the desktop, though I don't understand why it considers this a download when it's a database on my computer.) Might it be doing this due to PHPAdmin being a WebPage application and thus being served by your Web Server making the file need to be a FTP Download g? Well, that's as good an explanation as any. :) It isn't that big a deal; at least I know where I can find everything I download, and I can always copy and rename them and move them somewhere else if necessary. It just confused me this time around. __ 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]
