RE: Query Problem
Ok, Thanks for all Roger. -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: viernes, 22 de abril de 2005 4:06 Para: Dto. Sistemas de Unitel CC: mysql@lists.mysql.com Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. There should be no problem with joining two tables based on columns with different names. productos.prod_id=indexes.id should work. Both columns could be indexed, (in two separate indexes, of course, as they are in two separate tables), but only one index will be used, depending on the join order. It does not matter if you write productos.prod_id=indexes.id or indexes.id=productos.prod_id, and it does not matter if you write FROM productos,indexes or FROM indexes,productos (unless STRAIGHT_JOIN is used). In this case (se earlier posts in this thread) the table named indexes should be read first, then productos. That means an index on productos.prod_id will be used, if available. The name of the column in the productos table or the name of the related column in the indexes table does not matter. The = character in the ON clause or in the WHERE clause dictates which columns are related, not the name of the columns. I don't know why your index did not work at first. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String of 150 queries fast locally, but takes much longer accross cross connect
Hello, We are running FreeBSD on two Dual 2.5GHZ Xeon's each with 2 gigs of ram. One is our web server and the other our mySQL database server. The machines are connected directly with a cross connect cable. We are seeing transfer speeds of 8mb/s with scp between the two of them. Also, pinging is fast. su-2.05b# ping db0 PING db0 (192.168.0.1): 56 data bytes 64 bytes from 192.168.0.1: icmp_seq=0 ttl=64 time=0.097 ms 64 bytes from 192.168.0.1: icmp_seq=1 ttl=64 time=0.092 ms 64 bytes from 192.168.0.1: icmp_seq=2 ttl=64 time=0.117 ms 64 bytes from 192.168.0.1: icmp_seq=3 ttl=64 time=0.111 ms 64 bytes from 192.168.0.1: icmp_seq=4 ttl=64 time=0.102 ms 64 bytes from 192.168.0.1: icmp_seq=5 ttl=64 time=0.090 ms 64 bytes from 192.168.0.1: icmp_seq=6 ttl=64 time=0.102 ms 64 bytes from 192.168.0.1: icmp_seq=7 ttl=64 time=0.105 ms ^C --- db0 ping statistics --- 8 packets transmitted, 8 packets received, 0% packet loss round-trip min/avg/max/stddev = 0.090/0.102/0.117/0.009 ms There is no indication that there is anything slow about the connection between the two of them. I have one page where a string of 150 very simple queries are run one after another. It always loaded fairly fast (~0.1 second load time) in the past on our old servers (same setup - one web and one database). Now the page is taking about 2 seconds to load. I used the Advanced PHP Debugger (APD) profiler to see what functions were taking the most time because we thought it might be something wrong with PHP. The mysql_connect and mysql_select_db were taking the most time, but why so much? Next I decided to time the queries from the command line on both the web server (where the page is loading slow) and locally on the database server: --- -bash-2.05b$ time mysql -h localhost -u [removed] -p[removed] -D [removed] queries [query results removed] real 0m0.026s user 0m0.000s sys 0m0.015s su-2.05b# time mysql -h db0 -u [removed] -p[removed] -D [removed] queries [query results removed] real 0m1.153s user 0m0.000s sys 0m0.020s --- You can see the huge time difference. If I swap out the db0 alias for 192.168.0.1 the local address of the database server, it takes the same amount of time. Does anyone have any clue as to why this might be happening? Any help would be VERY much appreciated. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 - 4.1 update killed my db!
Hello. What type of tables do you use? I haven't heard about significant changes in MyISAM format. For InnoDB you should check the manual about incompatible changes. If nothing helps I suggest you to upgrade using mysqldump. See: http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html m i l e s [EMAIL PROTECTED] wrote: Hi, I updated my 4.0 install to 4.1 and now NONE of my databases and tables show up at all. Im on OS X.3.9. All the files are there in the data folder, permissions are set, what did I do wrong ? Any suggestions as to how to get it back ? M i l e s. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: LongText Warning
Hello. There's a similar bug: http://bugs.mysql.com/bug.php?id=7654 But it was closed due to absence of feedback. You may reopen this bug. Check this field has the correct encoding. [EMAIL PROTECTED] wrote: I have a table with a field that has a field of type LONGTEXT. I try to insert a utf8 string with a length of 114544 and I get a warning that text got truncated. According to the doc, the size of LONGTEXT is much bigger than this. Any reason I get this warning? -Jalil -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: crushed innodb table
Hello. Tools for MyISAM tables won't help you with InnoDB. See: http://dev.mysql.com/doc/mysql/en/gone-away.html iv [EMAIL PROTECTED] wrote: hi I've got a problem with a crushed innodb table (as i think) When I'm trying to make a backup, something like this appears: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `adstats` at row 34342; trying to check table: mysql check table adstats extended; ERROR 2013 (HY000): Lost connection to MySQL server during query repairing that: mysql repair table adstats; phpads.adstats | repair | note | The handler for the table doesn't support repair the same is going on with repair table ads_adstats USE_FRM I have also tried mysqlchk and myisamchk with -r and -o options. It didn't work, sinse that adstats is the innodb table.. myisamchk: error: 'adstats.frm' is not a MyISAM-table # mysqlcheck -r phpads adstats note : The handler for the table doesn't support repair Any ideas how to deal with the problem? I have read through about a hundred threads, but i haven't find a method of repairing innodb tables; and only in one place I've found a problem similar to mine, but without a solution Thanks in advance, iv iv at fnet dot pl -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: ERROR 2005
Hello. MySQL CC is no longer under development and could have unfixed bugs. However, usually it works for me. What did you do? Were you trying to connect to host 'abc'? Ercilio Almeida [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 10 lines --] I$ve installed MYSQL CONTROL CENTER 0.9.4 beta, when i tried to test the aplication, i get this error ERROR 2005: Unknow MYSQL Server Host 'abc'/(11001). Thanks, i'm waiting your answer as soon as possible. Ercilio - Yahoo! Acesso Gr$tis: Internet r$pida e gr$tis. Instale o discador agora! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
UTF-8 problems in text fields
MySQL: 4.1.11 OS: Solaris I have a database that stores Japanese in utf-8. I have NO problems if the field is defined as VARCHAR, but if the field is defined as TEXT any data after an extended character (Japanese, special symbols etc.) gets truncated. Looking at the archives I ran the following commands: mysql show variables like '%char%'; +--+---+ | Variable_name| Value | +--+---+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/4.1.11/share/mysql/charsets/ | +--+---+ 7 rows in set (0.00 sec) I will spare you the details but I also checked the character set and collation for the table and the field and they were UTF-8 and utf_general_ci. Any idea how to overcome this problem? Thanks in advance! DJA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mygrating from 3.23 to 4.1
Mário Gamito wrote: Hi, For years i've been using MySQL 3.23, but now that i'm about to reinstall my company's web server, i've decided that it's time to go to 4.1 I've read quite a few things in the web about migrating the databases, *including the mysql one*, but each article i read, pointed me in a different direction. Tryed to do the migration at my home computer, but... no good. What is indeed the right way to do this *full* databases migration from 3.3.28 to 4.1.11 ? I would suggest you to read : http://dev.mysql.com/doc/mysql/en/upgrade.html and check between each version what incompatibility might hit you. and then do the upgrade (either with a mysaldump, or just by upgrading your binary) Any help would be apreciated. Warm Regards, -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 - 4.1 update killed my db!
MySql creates a directory for each version and a symbolic link to the new installed version. If you open the terminal and do a: ls -l /usr/local/ there should be the old releases, the new one and a symbolic link 'mysql'. Look at the data directory in the old release directory; if you find your tables here then stop mysql, copy your tables (directory) with a 'cp -pr' (preserve recursive) to mysql/data and restart mysql. Hope this solve your problem. Santino Cusimano At 15:35 -0700 21-04-2005, m i l e s wrote: Hi, I updated my 4.0 install to 4.1 and now NONE of my databases and tables show up at all. Im on OS X.3.9. All the files are there in the data folder, permissions are set, what did I do wrong ? Any suggestions as to how to get it back ? M i l e s. -- 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]
concat multirow subselect
Hi, it is possible to subj? I have two tables. create table aaa (id int auto_increment not null, title varchar(255), primary key (id)); create table bbb (id int auto_increment not null, fk_aaa int not null, detail varchar(255), primary key (id)); insert into aaa values (1, 'aaa'), (2, 'bbb'); insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789'); I need display table aaa with last column concat values from bbb. Like this: | 1 | 'aaa' | '123 456' | | 2 | 'bbb' | '789' | It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use stored procedures? Stano. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat multirow subselect
Stano Paska wrote: Hi, it is possible to subj? I have two tables. create table aaa (id int auto_increment not null, title varchar(255), primary key (id)); create table bbb (id int auto_increment not null, fk_aaa int not null, detail varchar(255), primary key (id)); insert into aaa values (1, 'aaa'), (2, 'bbb'); insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789'); I need display table aaa with last column concat values from bbb. Like this: | 1 | 'aaa' | '123 456' | | 2 | 'bbb' | '789' | It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use stored procedures? Stano. In general, you can simply have your app output a newline, id, and title only when the id changes. Starting with 4.1, you can get the same result using GROUP_CONCAT(). See the manual for the details http://dev.mysql.com/doc/mysql/en/group-by-functions.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat multirow subselect
Michael Stassen wrote: Stano Paska wrote: Hi, it is possible to subj? I have two tables. create table aaa (id int auto_increment not null, title varchar(255), primary key (id)); create table bbb (id int auto_increment not null, fk_aaa int not null, detail varchar(255), primary key (id)); insert into aaa values (1, 'aaa'), (2, 'bbb'); insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789'); I need display table aaa with last column concat values from bbb. Like this: | 1 | 'aaa' | '123 456' | | 2 | 'bbb' | '789' | It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use stored procedures? Stano. In general, you can simply have your app output a newline, id, and title only when the id changes. Starting with 4.1, you can get the same result using GROUP_CONCAT(). See the manual for the details http://dev.mysql.com/doc/mysql/en/group-by-functions.html. Michael Yes, this is what I need. I didn't read this part of manual. :-( Thank you. S. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_fix_privilege_tables error
Would it be possible to add:
ENGINE=MyISAM
To all the CREATE TABLE statements in the mysql_fix_privilege_tables
script? The server (tested with 5.0.3 and 5.0.4) crashes when
creating/altering these tables if the following is in /etc/my.cnf:
default-table-type=innodb
I had to drop all the new tables and added ENGINE=MyISAM to the CREATE
TABLE statements, re-ran the script and it worked fine.
Here is an example crash report:
050422 9:19:43InnoDB: Assertion failure in thread 245771 in file
../include/data0type.ic line 466
InnoDB: Failing assertion: type-len % type-mbmaxlen == 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is
definitely wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size +
sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8ab7a70
Attempting backtrace. You can use the following information to find
out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe5f3938, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8180bef
0xb7e48c85
0x8295abc
0x829492a
0x829286c
0x82af069
0x82ae30c
0x82c5f99
0x823754f
0x8233119
0x8227b3f
0x8210552
0x823cb76
0x823f164
0x8196522
0x819d604
0x8194278
0x8193d85
0x8193192
0xb7e4354e
0xb7d71b8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html
and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please
do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8ae2ae0 = ALTER TABLE time_zone
MODIFY Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci
DEFAULT 'N' NOT NULL
thd-thread_id=6
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb, optimizer and outer join
Boyd, You can tell Hibernate not to use outer-joins by setting hibernate.use_outer_join to false in the hibernate configuration properties file. It's an always-never proposition. Of course, you can code your own queries using the Hibernate Query object to write your own when you know you do need one (and you still get the benefit of the relational-object mapping). I won't answer the question about the Innodb optimizer, as I don't know the answer. David Boyd E. Hemphill wrote: We are considering using Hibernate as a persistence layer to our web application. It seems to only want to do outer joins and this concerns me b/c they can be expensive. I created the following benchmark experiment and learned that the explain plan for the two constrained queries is the same. What I would like to know is can I depend on the performance being the same, or is the optimizer doing something different b/c of the outer join? I seem to remember something about it not using the index all the time or forcing a full table scan in some cases. Since Hibernate seems to using only an outer join rather than a join, I would like this concern put to rest. Thanks for any insight. Boyd create table foo ( foo_id int unsigned not null auto_increment primary key, foo_sn varchar(15), ) ; create table foo_child ( foo_child_id int unsigned not null auto_increment primary key, foo_id int unsigned not null, foo_child_sn varchar(15), index fk_foo$foo_child (foo_id) ) ; insert into foo values (1,'a'), (2,'b'), (3,'c'), (4,'d') ; insert into foo_child values (1,1,'z'), (2,1,'y'), (3,2,'x'), (4,3,'w'), (5,9,'v bad 1'), (6,9,'v bad 2'), (7,3,'t'), (8,4,'s') ; -- unconstrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id select * from foo_child fc left join foo f on fc.foo_id = f.foo_id -- constrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 select * from foo_child fc left join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 x 405 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and FreeBSD instability
We've been using mysql version 4.7 on a FreeBSD 4.10 machine for the past few months, and while we've been putting a lot of load onto the DB, it's been stable. This past weekend, our sysadmin updated FreeBSD from 4.10 to 4.11, at which point mysql began having serious problems. The DB itself is fine, but the process in FreeBSD instead of spending CPU time ends up hung in a state of biord, during which NO database operations can take place. From what I've been able to tell, this is a blocking I/O state, but what we can't figure out is why it's suddenly doing this, and how to get it to stop. Other than standard start-stop-reboot and so on, here are several remedies we've tried: rollback to BSD 4.10. Blocking state remains. Upgrade mysql to latest release (also 4.11): Blocking state goes from 90% of the time to 50% or so, but remains. The next step we're going to try is to switch out hardware to a BSD 5 machine and hope that solves the issue. But we've had no luck trying to figure out what's happening. Other states we see are ffsfsn and the occasional sbwait. Has anyone else had a similar issue, and if so how did you address it? Thanks! -- Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this table lock?
Reto Breitenmoser wrote: Hi In the output from the show innodb monitor I can see, that I have a table lock on a table. But, I never set a table lock on a table (only row locks). Do I misinterpret the output or what causes this table lock? thanks Reto TRANSACTIONS Trx id counter 0 95338708 Purge done for trx's n:o 0 95338686 undo n:o 0 0 History list length 10 Total number of lock structs in row lock hash table 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 95338703, not started, OS thread id 1764 MySQL thread id 43, query id 1028436 localhost 127.0.0.1 ales ---TRANSACTION 0 95338693, not started, OS thread id 3184 MySQL thread id 47, query id 1029658 localhost 127.0.0.1 ales ---TRANSACTION 0 95338705, not started, OS thread id 2720 MySQL thread id 45, query id 1028497 localhost 127.0.0.1 ales ---TRANSACTION 0 95338701, not started, OS thread id 2852 MySQL thread id 44, query id 1028430 localhost 127.0.0.1 ales ---TRANSACTION 0 95338380, not started, OS thread id 2960 MySQL thread id 10, query id 973614 localhost 127.0.0.1 root ---TRANSACTION 0 0, not started, OS thread id 1228 MySQL thread id 3, query id 1029917 localhost 127.0.0.1 root ---TRANSACTION 0 95338707, ACTIVE 0 sec, OS thread id 576 25 lock struct(s), heap size 2496, undo log entries 700 MySQL thread id 46, query id 1029926 localhost 127.0.0.1 ales query end update PARAMETER set PARAMETERIZEDELEM_ID=?, PARAMETER_IND=? where PARAMETER_ID=? TABLE LOCK table `pco/agentobject` trx id 0 95338707 lock mode IX TABLE LOCK table `pco/parameterizedelement` trx id 0 95338707 lock mode IX RECORD LOCKS space id 0 page no 6400 n bits 240 index `PRIMARY` of table `pco/agentobject` trx id 0 95338707 lock_mode X locks rec but not gap Record lock, heap no 174 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 862e; asc .;; 1: len 6; hex 05aec0d3; asc;; 2: len 7; hex 8019090084; asc ;; 3: SQL NULL, size 0 ; 4: SQL NULL, size 0 ; Record lock, heap no 175 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 862f; asc /;; 1: len 6; hex 05aec0d3; asc;; 2: len 7; hex 80190900de; asc ;; 3: len 10; hex 636c69656e74305f7332; asc client0_s2;; 4: len 7; hex 506c61747a2030; asc Platz 0;; Record lock, heap no 176 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 8630; asc 0;; 1: len 6; hex 05aec0d3; asc;; 2: len 7; hex 8019090138; asc 8;; 3: len 13; hex 4c6f616446726f6d456e747279; asc LoadFromEntry;; 4: len 15; hex 67726f7570206d6f64656c6c696e67; asc group modelling;; Record lock, heap no 177 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 8631; asc 1;; 1: len 6; hex Reto, You might want to watch the InnoDB Writes blocking Reads message thread on the list. It looks like you and Andy are having the same issues with innodb tables being locked instead of rows being locked. I don't have any idea... walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL to XML
Hi list, does it possible for MySQL to generate XML in the followin format: table name=ServiceType column name=idTipoServicio primaryKey=true required=true type=VARCHAR size=10/ column name=nombre required=true type=VARCHAR size=255/ column name=costo required=true type=FLOAT size=9/ column name=idGrupo required=true type=INTEGER/ column name=activa required=true type=BOOLEANINT/ foreign-key foreignTable=Grupo onUpdate=none onDelete=none reference foreign=idGrupo local=idGrupo/ /foreign-key /table This XML is the structure of the ServiceType table, I'll hope that you can help me Thnx in advanced Greetings P.S. Any suggestions (tools) will be appreciated -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Variables
I'm having trouble using user variables and I hope someone can help, My test environment is MySQL 4.1.11 on WindowsXP with MyODBC 3.51.11 If I open a command line client, I can do this mysql SET @A='Test'; Query OK, 0 rows affected (0.00 sec) mysql Select @A; +--+ | @A | +--+ | Test | +--+ 1 row in set (0.00 sec) If I run the following in MySQLFront v3.1 Set @A='Test'; Select @A; I get back same result +--+ | @A | +--+ | Test | +--+ If I run the same query in MySQL Query Browser v1.1.6 I get this, ErrNo 1060, 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 @A' at line 1 and If I run the same query in my application I get the same error as the Query Browser. Anyone know how I can get my application to give me what I'm looking for? Thanks!
Re: InnoDB Writes blocking Reads
Andy McCurdy wrote: I forgot to mention: we're running mysql version 4.0.23-standard-log -- Official MySQL-standard binary. Here's the innodb status output during a problematic period. = 050421 15:29:46 INNODB MONITOR OUTPUT = Per second averages calculated from the last 26 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1330878, signal count 1241079 Mutex spin waits 16157526, rounds 105045131, OS waits 692467 RW-shared spins 691802, OS waits 328867; RW-excl spins 91394, OS waits 34657 LATEST DETECTED DEADLOCK 050418 14:46:01 *** (1) TRANSACTION: TRANSACTION 0 52471953, ACTIVE 0 sec, process no 5468, OS thread id 2625171473 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 7832890, query id 51121416 host1 10.15.0.76 username Updating UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of table `gne/pm_message` trx id 0 52471953 lock_mode X waiting Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs FALSE; info bits 32 0: len 4; hex 002234fb; asc 4 ;; 1: len 6; hex 0320a88f; asc ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex 3139343432333 33535353937373535313334383739393833323236393236; asc 194423355597755134879983226926;...(truncated); 4: len 30; hex 3230333739383339383930343339303733 30363132343136363636363637; asc 20379839890439073061241667;...(truncated); 5: len 17; hex 417474656e74696f6e204d656d62657273; asc Attention Membe rs;; 6: len 30; hex 492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I want you to start posting he;...(truncated); 7: len 13; hex 3 231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc ;; 9: len 1; hex 81; asc ;; 10: len 8; hex 8000123c5a0d1524; asc Z $;; 11 : len 8; hex 8000123c59fd8369; ascY i;; *** (2) TRANSACTION: TRANSACTION 0 52471952, ACTIVE 0 sec, process no 5476, OS thread id 2625204248 starting index read, thread declared inside InnoDB 0 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 7832891, query id 51121414 host2 10.15.0.71 username Updating UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of table `gne/pm_message` trx id 0 52471952 lock_mode X locks rec but not gap Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs FALSE; info bits 32 0: len 4; hex 002234fb; asc 4 ;; 1: len 6; hex 0320a88f; asc ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex 3139343432333 33535353937373535313334383739393833323236393236; asc 194423355597755134879983226926;...(truncated); 4: len 30; hex 3230333739383339383930343339303733 30363132343136363636363637; asc 20379839890439073061241667;...(truncated); 5: len 17; hex 417474656e74696f6e204d656d62657273; asc Attention Membe rs;; 6: len 30; hex 492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I want you to start posting he;...(truncated); 7: len 13; hex 3 231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc ;; 9: len 1; hex 81; asc ;; 10: len 8; hex 8000123c5a0d1524; asc Z $;; 11 : len 8; hex 8000123c59fd8369; ascY i;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of table `gne/pm_message` trx id 0 52471952 lock_mode X waiting Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs FALSE; info bits 32 0: len 4; hex 002234fb; asc 4 ;; 1: len 6; hex 0320a88f; asc ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex 3139343432333 33535353937373535313334383739393833323236393236; asc 194423355597755134879983226926;...(truncated); 4: len 30; hex 3230333739383339383930343339303733 30363132343136363636363637; asc 20379839890439073061241667;...(truncated); 5: len 17; hex 417474656e74696f6e204d656d62657273; asc Attention Membe rs;; 6: len 30; hex 492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I want you to start posting he;...(truncated); 7: len 13; hex 3 231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc ;; 9: len 1; hex 81; asc ;; 10: len 8; hex 8000123c5a0d1524; asc Z $;; 11 : len 8; hex 8000123c59fd8369; ascY i;; *** WE ROLL BACK TRANSACTION (2) TRANSACTIONS Trx id counter 0 58483796 Purge done for trx's n:o 0 58483765 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 13939, OS thread id 46686284 MySQL thread id 12345108, query id 80171520 localhost mccurdya show innodb status ---TRANSACTION 0 58483795, not started, process no 13872, OS thread id 46432286 mysql
Write to a mysql table from Excel
Is it possible to write to a MySQL table from Excel? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
One problem I've encountered in the past creating CSV files from Excel is with fields that exceed 256 or 258 characters. The fields end up truncated in the CSV file. A script to directly access the data in Excel and move it to MySQL is appropriate, but can be a lot of work if you only need to do the import one or twice. Although this is somewhat convoluted it works for me. Start MS Access and create linked tables to you MySQL DB. Then import the data from Excel into Access. They're integrated fairly well so the import is basically a couple of button clicks. Then you're done. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- 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: extract numeric value from a string.
Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++ | tag| num_part | rest_of_string | ++--++ | 1foo |1 | foo| | 23bar | 23 | bar| | 234baz | 234 | baz| ++--++ 3 rows in set (0.00 sec) Eamon Daly - Original Message - From: dixie [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string. Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it? Tks in advance Paolo -- dixie [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]
innodb, optimizer and outer join
We are considering using Hibernate as a persistence layer to our web application. It seems to only want to do outer joins and this concerns me b/c they can be expensive. I created the following benchmark experiment and learned that the explain plan for the two constrained queries is the same. What I would like to know is can I depend on the performance being the same, or is the optimizer doing something different b/c of the outer join? I seem to remember something about it not using the index all the time or forcing a full table scan in some cases. Since Hibernate seems to using only an outer join rather than a join, I would like this concern put to rest. Thanks for any insight. Boyd create table foo ( foo_id int unsigned not null auto_increment primary key, foo_sn varchar(15), ) ; create table foo_child ( foo_child_id int unsigned not null auto_increment primary key, foo_id int unsigned not null, foo_child_sn varchar(15), index fk_foo$foo_child (foo_id) ) ; insert into foo values (1,'a'), (2,'b'), (3,'c'), (4,'d') ; insert into foo_child values (1,1,'z'), (2,1,'y'), (3,2,'x'), (4,3,'w'), (5,9,'v bad 1'), (6,9,'v bad 2'), (7,3,'t'), (8,4,'s') ; -- unconstrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id select * from foo_child fc left join foo f on fc.foo_id = f.foo_id -- constrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 select * from foo_child fc left join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 x 405
problem with update statement
Hello, all: In a MyISAM table, I have a column named MAC, of type VARCHAR(17). This field is used to hold MAC addresses of computers' network interface cards. These MAC addresses are in the form XX:XX:XX:XX:XX:XX, where X can be either a number or an uppercase letter. I can run select * from table where MAC='00:04:FB:23:5A:44' and the correct record is returned. However, performing update table set port_index='123' where MAC='00:04:FB:23:5A:44' does not work as I expected. It does update the correct record, but also updates all other records whose MAC field is empty. When I originally created this field, I used type VARCHAR (17), null, default value NULL. In troubleshooting this, I have since change the type to CHAR(17), not null, empty default value. Still have the same problem. Does anyone know what's going on here, and how I could correct this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is this table lock?
Hi In the output from the show innodb monitor I can see, that I have a table lock on a table. But, I never set a table lock on a table (only row locks). Do I misinterpret the output or what causes this table lock? thanks Reto TRANSACTIONS Trx id counter 0 95338708 Purge done for trx's n:o 0 95338686 undo n:o 0 0 History list length 10 Total number of lock structs in row lock hash table 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 95338703, not started, OS thread id 1764 MySQL thread id 43, query id 1028436 localhost 127.0.0.1 ales ---TRANSACTION 0 95338693, not started, OS thread id 3184 MySQL thread id 47, query id 1029658 localhost 127.0.0.1 ales ---TRANSACTION 0 95338705, not started, OS thread id 2720 MySQL thread id 45, query id 1028497 localhost 127.0.0.1 ales ---TRANSACTION 0 95338701, not started, OS thread id 2852 MySQL thread id 44, query id 1028430 localhost 127.0.0.1 ales ---TRANSACTION 0 95338380, not started, OS thread id 2960 MySQL thread id 10, query id 973614 localhost 127.0.0.1 root ---TRANSACTION 0 0, not started, OS thread id 1228 MySQL thread id 3, query id 1029917 localhost 127.0.0.1 root ---TRANSACTION 0 95338707, ACTIVE 0 sec, OS thread id 576 25 lock struct(s), heap size 2496, undo log entries 700 MySQL thread id 46, query id 1029926 localhost 127.0.0.1 ales query end update PARAMETER set PARAMETERIZEDELEM_ID=?, PARAMETER_IND=? where PARAMETER_ID=? TABLE LOCK table `pco/agentobject` trx id 0 95338707 lock mode IX TABLE LOCK table `pco/parameterizedelement` trx id 0 95338707 lock mode IX RECORD LOCKS space id 0 page no 6400 n bits 240 index `PRIMARY` of table `pco/agentobject` trx id 0 95338707 lock_mode X locks rec but not gap Record lock, heap no 174 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 862e; asc .;; 1: len 6; hex 05aec0d3; asc;; 2: len 7; hex 8019090084; asc ;; 3: SQL NULL, size 0 ; 4: SQL NULL, size 0 ; Record lock, heap no 175 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 862f; asc /;; 1: len 6; hex 05aec0d3; asc;; 2: len 7; hex 80190900de; asc ;; 3: len 10; hex 636c69656e74305f7332; asc client0_s2;; 4: len 7; hex 506c61747a2030; asc Platz 0;; Record lock, heap no 176 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 8630; asc 0;; 1: len 6; hex 05aec0d3; asc;; 2: len 7; hex 8019090138; asc 8;; 3: len 13; hex 4c6f616446726f6d456e747279; asc LoadFromEntry;; 4: len 15; hex 67726f7570206d6f64656c6c696e67; asc group modelling;; Record lock, heap no 177 PHYSICAL RECORD: n_fields 5; 1-byte offs TRUE; info bits 0 0: len 8; hex 8631; asc 1;; 1: len 6; hex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Writes blocking Reads
Andy, Can you send me the table layout and the script ? I'd like to try it here on one my test systems running 4.0.23. walt Andy McCurdy wrote: I haven't seen any other replies as of yet. After looking closer at the innodb status dump, the last known deadlock occured several days ago... so I don't think deadlocks are my main issue.. The confusing part of this is that when writes happen, it seems the entire database is locking. To test this, I created a new inno table with a single column on my production env. I used a script insert a record into this table every second. When any production write happened, this script would block for 4-5 seconds until the other write finished. The only things this test inno table had in common with our prod tables were: - in the same database - in the same datafile - governed by the same mysqld server settings No other query was touching this test table. So one of those three things seems to be the cause... I just don't know what to do at this point. -andy On 4/22/05, kernel [EMAIL PROTECTED] wrote: Andy McCurdy wrote: I forgot to mention: we're running mysql version 4.0.23-standard-log -- Official MySQL-standard binary. Here's the innodb status output during a problematic period. = 050421 15:29:46 INNODB MONITOR OUTPUT = Per second averages calculated from the last 26 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1330878, signal count 1241079 Mutex spin waits 16157526, rounds 105045131, OS waits 692467 RW-shared spins 691802, OS waits 328867; RW-excl spins 91394, OS waits 34657 LATEST DETECTED DEADLOCK 050418 14:46:01 *** (1) TRANSACTION: TRANSACTION 0 52471953, ACTIVE 0 sec, process no 5468, OS thread id 2625171473 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 7832890, query id 51121416 host1 10.15.0.76 username Updating UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of table `gne/pm_message` trx id 0 52471953 lock_mode X waiting Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs FALSE; info bits 32 0: len 4; hex 002234fb; asc 4 ;; 1: len 6; hex 0320a88f; asc ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex 3139343432333 33535353937373535313334383739393833323236393236; asc 194423355597755134879983226926;...(truncated); 4: len 30; hex 3230333739383339383930343339303733 30363132343136363636363637; asc 20379839890439073061241667;...(truncated); 5: len 17; hex 417474656e74696f6e204d656d62657273; asc Attention Membe rs;; 6: len 30; hex 492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I want you to start posting he;...(truncated); 7: len 13; hex 3 231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc ;; 9: len 1; hex 81; asc ;; 10: len 8; hex 8000123c5a0d1524; asc Z $;; 11 : len 8; hex 8000123c59fd8369; ascY i;; *** (2) TRANSACTION: TRANSACTION 0 52471952, ACTIVE 0 sec, process no 5476, OS thread id 2625204248 starting index read, thread declared inside InnoDB 0 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 7832891, query id 51121414 host2 10.15.0.71 username Updating UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of table `gne/pm_message` trx id 0 52471952 lock_mode X locks rec but not gap Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs FALSE; info bits 32 0: len 4; hex 002234fb; asc 4 ;; 1: len 6; hex 0320a88f; asc ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex 3139343432333 33535353937373535313334383739393833323236393236; asc 194423355597755134879983226926;...(truncated); 4: len 30; hex 3230333739383339383930343339303733 30363132343136363636363637; asc 20379839890439073061241667;...(truncated); 5: len 17; hex 417474656e74696f6e204d656d62657273; asc Attention Membe rs;; 6: len 30; hex 492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I want you to start posting he;...(truncated); 7: len 13; hex 3 231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc ;; 9: len 1; hex 81; asc ;; 10: len 8; hex 8000123c5a0d1524; asc Z $;; 11 : len 8; hex 8000123c59fd8369; ascY i;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of table `gne/pm_message` trx id 0 52471952 lock_mode X waiting Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs FALSE; info bits 32 0: len 4; hex 002234fb; asc 4 ;; 1: len 6; hex 0320a88f; asc ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex 3139343432333 33535353937373535313334383739393833323236393236; asc
RE: Write to a mysql table from Excel
[snip] Is it possible to write to a MySQL table from Excel? [/snip] Sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
At 03:12 PM 4/22/05, Jay Blanchard wrote: [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. Don't be so hasty to criticise. I took the original question to mean While I have an existing spreadsheet open in EXCEL, can I cause a selected row in a MySQL table to be updated?. This is quite a different question than How can I convert one sheet from an EXCEL file into a new MySQL table?. The later can easily be done several ways as you suggest, the former cannot. Can the former be done with ODBC? I didn't find any answers on google. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
[snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, 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 @A' at line 1
and If I run the same query in my application I get the same error as the Query
Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
Get a beginners book then rather than posting messages. -Original Message- From: Huang, Ou [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 5:17 PM To: Jay Blanchard; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel Oh well, I am just not smart as you are. Sorry, I am a new comer in the Geek's world. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- 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: Write to a mysql table from Excel
Oh well, I am just not smart as you are. Sorry, I am a new comer in the Geek's world. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB: How do I know how much free space there is left on a raw device?
Hi list, Just a simple question. How do I know how much free space there is left on a device assigned to InnoDB? Jarle -- Jarle Aase email: [EMAIL PROTECTED] Author of freeware. http://www.jgaa.com news:alt.comp.jgaa War FTP Daemon: http://www.warftp.org War FTP Daemon FAQ: http://www.warftp.org/faq/warfaq.htm Jgaa's PGP key: http://war.jgaa.com/pgp NB: If you reply to this message, please include all relevant information from the conversation in your reply. Thanks. no need to argue - just kill'em all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Write to a mysql table from Excel
Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:00 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Is it possible to write to a MySQL table from Excel? [/snip] Sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: How do I know how much free space there is left on a raw device?
At 17:48 +0200 4/22/05, Jarle Aase wrote: Hi list, Just a simple question. How do I know how much free space there is left on a device assigned to InnoDB? Depends on what you mean by free space. Do you mean not space on the device not assigned to the InnoDB tablespace file, or space free within the InnoDB tablespace? If the latter, SHOW TABLE STATUS for any InnoDB table in the tablespace shows the approximate free space, but this is for the entire tablespace. If your tablespace has other files besides the raw partition, the free space value won't answer your question. -- 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: grant question
At 11:19 -0500 4/22/05, Scott Purcell wrote:
I am here in the docs.
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
I have a database with tables, and I want to set up a user with a
password that I can use to display this database and tables in a
webapp.
I do not want to use the root/password for connecting. So the docs
say to use this command. Problem is I cannot find what to put in for
the priv_type and I am unclear exactly how to pull this together. If
all tables are allowed to be used can someone help me out with this?
If you want the account to have read-only access, priv_type should be
SELECT.
GRANT SELECT ON db_name.* TO 'some_user'@'some_host'
IDENTIFIED BY 'some_password';
--
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]
grant question
I am here in the docs.
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
I have a database with tables, and I want to set up a user with a password
that I can use to display this database and tables in a webapp.
I do not want to use the root/password for connecting. So the docs say to use
this command. Problem is I cannot find what to put in for the priv_type and I
am unclear exactly how to pull this together. If all tables are allowed to be
used can someone help me out with this?
Thanks,
Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Thanks for the reply,
So is there anyway to use User Variables with a single connection. All my apps
are in VB6 and VBA. They all take a query, open a connection, run query, fill
array from query results, close connection and pass back the array. Because of
backward compatibility there's no way I can change them to do otherwise.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, 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 @A' at line 1
and If I run the same query in my application I get the same error as the
Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
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]
