MySQl prints only first 1032 character in output line
hello list, this is a MySQL realted question I have a mySQL table with 15 columns. I query that table and group (using GROUP_CONCAT) them based on one filed (say coloumn1). I get the results. But, the result is truncated in some of the lines. It gets truncated exactly after 1032th character of that line. Truncation happens in lines having more than 1032 characters. how can solve this ? is this 1032 is limit set by mysql or linux (i use RH 7.3). How to sort it out. -- Ra. Gowthaman, Graduate Student, Bioinformatics Lab, Malaria Research Group, ICGEB , New Delhi. INDIA Phone: 91-9811261804 91-11-26173184; 91-11-26189360 #extn 314 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 05:01, Sasha Pachev wrote: Innodb to my knowledge does not allocate very much locally per thread, and should not allocate anything at all if you are not doing any queries. That's what I thought. Based on the test results you have reported, I would put your libc as the primary suspect, I'm using the system libc which comes with Tru64 5.1B and the next one would be bad build/compiler bugs. I'm using the binary build as supplied by MySQL. I'm going to try compiling mysql myself, and see whether that makes any difference. I would suppose that --skip-innodb just changes some memory allocation patters on startup, which possibly avoid triggering the bug. Perhaps... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1236 corrupt binlog on master
I have been using the mysqlbinlog to investigate the 'saturn2-bin.051' binlog file.. I have come to pinpoint the problem position in the binlog, but don't know how i can bypass the corrupted segment. Please help. Thanks Arvind. -Original Message- From: Arvind Gangal [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 21:17 To: '[EMAIL PROTECTED]' Subject: Error 1236 corrupt binlog on master Hello Gurus, Anyone have any ideas as to how I should handle a case of the following error? 040324 21:02:05 Slave I/O thread: connected to master mailto:'[EMAIL PROTECTED]:3306' '[EMAIL PROTECTED]:3306', replication started in log 'saturn2-bin.051' at position 442744915 040324 21:02:05 Error in Log_event::read_log_event(): 'read error', data_len=657471024,event_type=61 040324 21:02:05 Slave SQL thread: I/O error reading event(errno: -1 cur_log-error: 1723122) 040324 21:02:05 Error reading relay log event: Aborting slave SQL thread because of partial event read 040324 21:02:05 Could not parse log event entry, check the master for binlog corruption This may also be a network problem, or just a bug in the master or slave code. 040324 21:02:05 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'satur n2-bin.051' position 442744915 040324 21:02:05 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040324 21:02:05 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040324 21:02:05 Slave I/O thread exiting, read up to log 'saturn2-bin.051', position 442744915 Replication seems to have stopped at that point and the slave keeps trying to connect and dies at with that error. I think that the binlog file 'saturn2-bin.051' on master has got courrupted. Is there any way i can start after the point of corruption in the binary file? Thanks, Arvind Arvind Gangal Senior Analyst Programmer address: 4 Buckingham Gate, London SW1E 6JP tel: +44 (0)20 7802 4306 fax: +44 (0)20 7802 9302 mob: 07734938242 email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] internet: http://www.lastminute.com/ http://www.lastminute.com do something lastminute.com This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 06:31, Heikki Tuuri wrote: you can use the command SHOW INNODB STATUS; to check how much memory InnoDB has allocated in total. Please report what it says at the time of the memory explosion. Well, that was informative, but in a negative sort of way. SHOW INNODB STATUS produced identical output when run immediately after server startup and when 50 idle connections were in place, and the virtual memory consumption had increased by around 2GB. You report that even 100 IDLE connections cause the memory explosion. I agree with Sasha that this probably is not a MySQL/InnoDB bug. I have not heard of a similar memory problem from anyone else. No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I have problem
You need to set a user in MySQL with privileges to that database. From the mysql console: mysql GRANT options ON database_name.* TO user@'217.219.201.2' IDENTIFIED BY 'password' USING GRANT OPTION; J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vb.net - mysql
Hello,
i write a front-end in vb.net and want to save data`s out of a text field
into a MySQL database.
But i have trouble
with
this.
-
a short example out of my source code to save data`s directley into MySQL.
Private Sub btnSpeichern_Click
Dim MyConString As String .
Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()
Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = insert into test(firma) values ('test')
MyConnection.Close()
-
How can i write into MySQL the data`s i type into a text field?
Regrads
Thorsten
--
+++ NEU bei GMX und erstmalig in Deutschland: TÜV-geprüfter Virenschutz +++
100% Virenerkennung nach Wildlist. Infos: http://www.gmx.net/virenschutz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysterious can't connect error message in our logs(2)
Sasha Pachev writes: Jigal van Hemert wrote: It's a bug. I would recommend to patch libmysql.c for now until MySQL developers fix it. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ Our client side uses select() but only when waiting for data, and not on teh connections to the socket. -- Sincerely, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB /_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator ___/ www.mysql.com Larnaca, Cyprus Meet the MySQL at User Conference ! (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext indices
Is it possible to fit an entire article (say, a newspaper article) into one cell of a MySQL database? When I tried load data local infile the file was imported into several rows! Perhaps I just don't understand the use of a fulltext index. --bhcesl Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time.
Re: fulltext indices
Brandon Carter wrote: Is it possible to fit an entire article (say, a newspaper article) into one cell of a MySQL database? When I tried load data local infile the file was imported into several rows! Perhaps I just don't understand the use of a fulltext index. --bhcesl Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. Absolutely. In an RD environment, I created a table with more than 7 million newspaper articles. Didn't use load data local infile, though, but a fairly simple Perl script. Regards, Kurt Haegeman Mediargus.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqladmin shutdown
Was wondering if anybody knows if there are advantages to using mysqladmin -pPassword shutdown compared to /etc/rc.d/init.d/mysqld stop? I have a script that was using mysqladmin to shutdown the daemon and then /etc/rc.d/init.d/mysqld start to restart it. Problem was, and I am not sure if it is related to it, but when restarting, it wasn't recreating the /var/lib/mysql/mysql.sock file. Strange though because I could run the mysqladmin shutdown on the command line and then the init.d/mysqld start and everything worked just fine. It was only doing it when running it through my script from a cron job. I could even run the script from the command line and it would work. It was just when I ran it through a cron job at 1am every morning. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: out of memory error and update problem
Chip Wiegand wrote: When running mysql from the command line (Putty terminal) and trying to do some updates the updates fail. First the query I am running - mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID between 75 and 100 AND warranty_temp.WarrantyID between 75 and 100; Query OK, 119 rows affected (50.97 sec) Rows matched: 14641 Changed: 119 Warnings: 0 This is not a join, it is a cartesian product of the 2 table ranges. The results of this query are that the OwnerName in record 75 is copied into the OwnerName for records 75 thru 100. That shouldn't be, there should be a differant OwnerName for each row being copied into each row of the target table. Now the table has 119 rows with the same OwnerName. mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID=warranty_temp.WarrantyID and warranty_old.WarrantyID between 75 and 100 ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 10:10, Tim Cutts wrote: No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... The version compiled natively on the machine does the same thing (although it uses a little less memory to start with since it's not statically linked). I suppose the next thing to try is the debug version. I've tried compiling the debug version myself without success, so I'll download the debug version from MySQL and try that. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
In support ?
Sorry if this is an obvious question but is version 3.23 still supported by MySQL AB. And if it still is is there a date when it will not be. One of our systems uses it but the users are not convinced about upgrading to version 4 Thanks Dave This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database dump query
Hi, anyone know what happens if, while I am doing a database dump, someone logs into the database and updates records, what records are trapped in the dump, or can the dump proceed under this circumstance...in other words must I knock all users off the database before the dump is done? Many thanks Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database dump query
On 25 Mar 2004, at 15:23, joe collins wrote: Hi, anyone know what happens if, while I am doing a database dump, someone logs into the database and updates records, what records are trapped in the dump, or can the dump proceed under this circumstance...in other words must I knock all users off the database before the dump is done? mysqldump, if used with the --lock-tables option (which is implied by --opt) obtains read locks, so queries attempting to update the database will block until the dump has finished. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: vb.net - mysql
Thorsten, i write a front-end in vb.net and want to save data`s out of a text field into a MySQL database. But i have trouble with this. On http://www.vbmysql.com/articles, you will find some great VB/MySQL-related tutorials. http://www.vbmysql.com/samplecode/simpleinsert.html is probably what you're looking for. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump exlcude table
Hi I want to perform a mysqldump (with the --add-drop flag) of an entire database that excludes 1 specific table only (named 'phpbb_config'). Can anyone supply the mysql for this? I know I can do this in reverse - that is specify all the tables i want to mysqldump, but this is inefficeint, as the database contains numerous tables: mysqldump --add-drop-table -u [username] -p[password] [database] [table_name1] [table_name2] ... [backup_file] Thanks K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl prints only first 1032 character in output line
gowthaman ramasamy [EMAIL PROTECTED] wrote: hello list, this is a MySQL realted question I have a mySQL table with 15 columns. I query that table and group (using GROUP_CONCAT) them based on one filed (say coloumn1). I get the results. But, the result is truncated in some of the lines. It gets truncated exactly after 1032th character of that line. Truncation happens in lines having more than 1032 characters. how can solve this ? is this 1032 is limit set by mysql or linux (i use RH 7.3). How to sort it out. Check value of group_concat_max_len system variable: SELECT @@session.group_concat_max_len; -- 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: Building super-smack on freebsd 4.9
[EMAIL PROTECTED] wrote:
Not sure if this is the place to post this but...
I'm trying to build super-smack-1.2 on freebsd 4.9 w/ MySQL 4.0.18 and
am
running into the following problems. Below are configure, make and gmake
output:
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ ./configure --with-mysql
creating cache ./config.cache
checking for a BSD compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... yes
checking for working aclocal... found
checking for working autoconf... found
checking for working automake... found
checking for working autoheader... found
checking for working makeinfo... found
checking for sh... /bin/sh
checking for gcc... gcc
checking whether the C compiler (gcc ) works... yes
checking whether the C compiler (gcc ) is a cross-compiler... no
checking whether we are using GNU C... yes
checking whether gcc accepts -g... yes
checking for c++... c++
checking whether the C++ compiler (c++ ) works... yes
checking whether the C++ compiler (c++ ) is a cross-compiler... no
checking whether we are using GNU C++... yes
checking whether c++ accepts -g... yes
checking how to run the C preprocessor... gcc -E
checking for a BSD compatible install... /usr/bin/install -c
checking whether ln -s works... yes
checking for flex... flex
checkin
g for flex... (cached) flex
checking for yywrap in -lfl... yes
checking lex output file root... lex.yy
checking whether yytext is a pointer... yes
checking for bison... no
checking for byacc... byacc
checking for compress in -lz... yes
checking for crypt in -lcrypt... yes
checking for crypt... yes
checking for libmysqlclient...
checking for mysql_real_connect in -lmysqlclient... yes
checking for mysql_real_connect in -lmysqlclient... (cached) yes
checking for mysql.h...
checking for ANSI C header files... yes
checking for sys/time.h... yes
checking for unistd.h... yes
checking whether time.h and sys/time.h may both be included... yes
checking return type of signal handlers... void
checking for gettimeofday... yes
checking for strerror... yes
updating cache ./config.cache
creating ./config.status
creating Makefile
creating src/Makefile
creating config.h
Building with the following options:
MySQL Support. yes
PostgreSQL Support no
Oracle Support no
If this is not what you intended, please re-run configure.
Thanks for using super-smack!
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ gmake
gmake all-recursive
gmake[1]: Entering directory `/usr/local/src/build/super-smack-1.2'
Making all in src
gmake[2]: Entering directory `/usr/local/src/build/super-smack-1.2/src'
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include -g -O2 -c
super-smack.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include -g -O2 -c client.cc
client.cc: In method `void Client::thread_sync()':
client.cc:347: `ERESTART' undeclared (first use this function)
client.cc:347: (Each undeclared identifier is reported only once
client.cc:347: for each function it appears in.)
gmake[2]: *** [client.o] Error 1
gmake[2]: Leaving directory `/usr/local/src/build/super-smack-1.2/src'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/usr/local/src/build/super-smack-1.2'
gmake: *** [all-recursive-am] Error 2
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ make
make all-recursive
Making all in src
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c client.cc
client.cc: In method `void Client::thread_sync()':
client.cc:347: `ERESTART' undeclared (first use this function)
client.cc:347: (Each undeclared identifier is reported only once
client.cc:347: for each function it appears in.)
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2/src.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
Hmm.. Looks like Jeremy did not quite fix everything on FreeBSD in version
1.2.
For a hack, change ERESTART to EINTR in client.cc for now until we've
figured it
out.
Ok, I did that. Gets further now but still doesn't finish building.
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ make
make all-recursive
Making all in src
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c client.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c engines.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c die.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c dictionary.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c
super-smack-yacc.cc
super-smack-yacc.yy:9: getopt.h: No such file or directory
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2/src.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
Thanks for
Re: fulltext indices
First, full text indices have nothing to do with importing the data, it's just a special type of indexing for searching. The reason you got several rows is that you probably did not specify delimiters. Thus, every time a return was encountered, a new record was created because return is the default record delimiter for imports. Read the manual for loading data, you can specify any delimiter for fields and records. For instance, for importing full documents I use ^^ for field delimiters and ~~ for record delimiters. On Mar 24, 2004, at 5:53 PM, Brandon Carter wrote: Is it possible to fit an entire article (say, a newspaper article) into one cell of a MySQL database? When I tried load data local infile the file was imported into several rows! Perhaps I just don't understand the use of a fulltext index. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query across two databases on the same server
yea, I thought it would be that easy too but it doesn't work. Any other ideas? Peter Lovatt [EMAIL PROTECTED] 3/24/04 3:53:03 PM select db1.table.field, db2.table.field where db1.table.someotherfield = db2.table.someotherfield Peter -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 23:45 To: [EMAIL PROTECTED] Subject: Query across two databases on the same server Is there any way to have a single select statement that can do a join across two databases on the same server? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication errors...
Thanks! I was hoping it was something that had already been found. I'll upgrade as soon as I can. Thanks, Brian -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 10:47 PM To: Stanton, Brian Cc: '[EMAIL PROTECTED]' Subject: Re: Replication errors... Stanton, Brian wrote: Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... I've seen it on my systems. The problem is a bug in DELETE in 4.0 that was fixed in 4.0.18. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl prints only first 1032 character in output line
On Thu, 2004-03-25 at 21:23, Victoria Reznichenko wrote: thank you dear victoria it says 1024. How can i increase it?. Check value of group_concat_max_len system variable: SELECT @@session.group_concat_max_len; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query across two databases on the same server
Ed Reed [EMAIL PROTECTED] wrote: yea, I thought it would be that easy too but it doesn't work. It should work. Did you get error message? Any other ideas? Peter Lovatt [EMAIL PROTECTED] 3/24/04 3:53:03 PM select db1.table.field, db2.table.field where db1.table.someotherfield = db2.table.someotherfield Peter -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 23:45 To: [EMAIL PROTECTED] Subject: Query across two databases on the same server Is there any way to have a single select statement that can do a join across two databases on the same server? -- 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: Query across two databases on the same server
It really should be that easy, though Peter's example is missing the FROM clause. I'm sure he just meant to show the syntax for db.table.column rather than a complete query. Why don't you tell us what version of mysql you have, the query you tried, and the result you got. Michael Ed Reed wrote: yea, I thought it would be that easy too but it doesn't work. Any other ideas? Peter Lovatt [EMAIL PROTECTED] 3/24/04 3:53:03 PM select db1.table.field, db2.table.field where db1.table.someotherfield = db2.table.someotherfield Peter -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 23:45 To: [EMAIL PROTECTED] Subject: Query across two databases on the same server Is there any way to have a single select statement that can do a join across two databases on the same server? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query across two databases on the same server
Ed Reed wrote: yea, I thought it would be that easy too but it doesn't work. Other than the fact that the given example needs a FROM clause, sure it works. What exactly are you trying and what is the exact incorrect result? select db1.table.field, db2.table.field FROM db1.table, db2.table where db1.table.someotherfield = db2.table.someotherfield -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl prints only first 1032 character in output line
gowthaman ramasamy [EMAIL PROTECTED] wrote: On Thu, 2004-03-25 at 21:23, Victoria Reznichenko wrote: thank you dear victoria it says 1024. How can i increase it?. Use SET command. For example: SET @@global.group_concat_max_len=5000; or SET @@session.group_concat_max_len=5000; Check value of group_concat_max_len system variable: SELECT @@session.group_concat_max_len; -- 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]
parsing a column
Hi, I would appreciate help on the following. I have a column in a table. Each entry in the column can have 0,1,2,3... maybe four entries in it. I would like to parse the column and grab each of the entries and feed it into another select statement. This entry that needs to be parsed would be found through a select statement. I would like to know what a good way is for doing this. For example an entry in the column may be AI732541 AI791498 AA508861 another entry may be AA508867 Basically, in the first example AI732541 AI791498 AA508861 would be the the answer ie. access_num for my first select statement Select access_num from inventory where prod_id = current_prod_id; I would like to parse the entry to allow me to access AI732541 to feed into another select statement find out the answer and capture in a variable. Then I would like to be able to access the next entry AI791498 and perform the same task of feeding it into another select statement and capturing the answer in a variable an so on. thanks in advance - Post your free ad now! Yahoo! Canada Personals
mysql.user does not reflect privileges granted correctly?
Greetings, I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: *** 1. row *** Host: localhost User: ltmuser Password: 132f058a4532ff99 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 I thought the *_priv fields should all have 'Y' rather than 'N'. I also tried 'flush privileges' but it did not help. What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? I can insert rows one by one into table ltm_enrollment as user 'ltmuser'@'localhost' without any problems. Would anybody tell me what's wrong? Thanks, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user does not reflect privileges granted correctly?
Bing Du wrote: I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: :: that 'ltm' != 'ltmuser' :-) Or is the example a typo? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user does not reflect privileges granted correctly?
Bing Du [EMAIL PROTECTED] wrote: Greetings, I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: *** 1. row *** Host: localhost User: ltmuser Password: 132f058a4532ff99 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 I thought the *_priv fields should all have 'Y' rather than 'N'. Table 'user' contains global level privileges, but you set permissions on the database level. Check 'db' table. I also tried 'flush privileges' but it did not help. What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? You must have FILE privilege. I can insert rows one by one into table ltm_enrollment as user 'ltmuser'@'localhost' without any problems. -- 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: Query across two databases on the same server
I found the problem. There was a hyphen in my database name. Re-created the database and re-imported my data, now everything works fine. Thanks to everyone. Egor Egorov [EMAIL PROTECTED] 3/25/04 8:19:04 AM Ed Reed [EMAIL PROTECTED] wrote: yea, I thought it would be that easy too but it doesn't work. It should work. Did you get error message? Any other ideas? Peter Lovatt [EMAIL PROTECTED] 3/24/04 3:53:03 PM select db1.table.field, db2.table.field where db1.table.someotherfield = db2.table.someotherfield Peter -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 23:45 To: [EMAIL PROTECTED] Subject: Query across two databases on the same server Is there any way to have a single select statement that can do a join across two databases on the same server? -- 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]
innodb deadlock detection failing?
Has anyone seen situations where innodb's deadlock detection fails to detect a deadlock, and things remain deadlocked until the lock wait timeout expires and the server returns a Lock wait timeout exceeded; Try restarting transaction, or have any ideas for why it may be happening? There are no mysql locks (ie. lock table ..., get_lock(), etc.) explicitly being used. I was under the impression that innodb's deadlock detection was guaranteed to always work if no mysql locks were involved. This is running 4.0.15a on Linux, all tables on the server (aside from the mysql database) are innodb. I have some innodb lock monitor dumps, but they aren't overly illuminating, at least to me... all the active transactions are blocking waiting for something, but I can't fully trace who is waiting for who since only 10 locks are printed for each one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Local installation of mysql
Dear all, My system specs are Sun Solaris 8. As I do not have root permission, is it possible to install mysql locally in my home drive ? I checked the manuals but there does not seem to answer my question. If so, what is the expected total size on disk and recommended steps (./configure, make, make --prefix=/my/home/ install : is this sufficient) ? Any hints or pointers are much appreciated. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
On 24-Mar-2004 Henrik Schröder wrote: snip No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) Best would be to use ENUM( '1','2','0') ... But otherwise -- (membershiptype % 2) DESC, membershiptype DESC -- or -- ELT(membershiptype + 1, '2', '0', '1') ASC -- or -- MAKE_SET(membershiptype, 'a','b') DESC Regards -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speeding up MySQL server
Hmm. Taking a stab in the dark here, but.. If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row? Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed? Depends on what you're doing, and what you're trying to achieve, really! Thanks, Matt. -Original Message- From: Jim Richardson [mailto:[EMAIL PROTECTED] Sent: 25 March 2004 03:17 To: [EMAIL PROTECTED] Subject: Re: Speeding up MySQL server On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql as cluster service, failover causes broken replication
Yes, the clients (appearently) read to the end of the previous file, and then sit there, while the server is writing to a new file. I was thinking this had to do with the unclean shutdown of MySQL-- perhapps it's something else. -Matt- Matt Sturtz wrote: Hello-- We're using Red Hat's cluster manager (RH AS 2.1, MySQL 4.0.16 RPM). Due to a problem within the cluster software that we're working on with Red Hat, the cluster fails over from one node to the other sometimes when it shouldn't (one node will reboot, services will fail over-- at this point we think it's probably related to IO on the shared quorum partitions). When service is restored some seconds later, the slaves won't start replicating from the newly created binary-log, instead continuing to read from the previous one (IE db-bin.002 is created when MySQL is restarted, but the slaves keep reading from the old file, db-bin.001). The only fix seems to be CHANGE MASTER TO..., which seems somewhat error prone. Anybody else running MySQL in this type of environment have any words of wisdom? Thanks in advance for any info... They should keep reading from the old one until they catch up. Do they fail to roll over to the next one after finishing the old one? If yes, it would be a bug. -- Sasha Pachev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting rows in all tables
On 25-Mar-2004 [EMAIL PROTECTED] wrote:
Not using perl or php I came up with:
mysqlshow -u user --status db_name | awk -F| '{print $2,$5}' | grep
-i pattern
Is there a query to do the same thing?
SHOW TABLE STATUS
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speeding up MySQL server
On Thu, Mar 25, 2004 at 07:21:04PM -, Matt Chatterley wrote: Hmm. Taking a stab in the dark here, but.. If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row? Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed? Depends on what you're doing, and what you're trying to achieve, really! If I understand you correctly, you are suggesting to create a temp table, and update that all the time, then add that sum to the perm table every once in a while? In this case, the counter is only checked (for stats purposes) once an hour, so updating it at 30min increments would be fine (at our hit rate, that's about 180,000 hits) which would save a fair amount of contention time on the main table (If I understand it correctly). I'll have to look into this. -- Jim Richardson http://www.eskimo.com/~warlock Madness takes its toll. Please have exact change ready. signature.asc Description: Digital signature
Dumping sql commands
Hi there, I am running a website on a host who has blocked access to mysqldump. Is there any way I can show all the sql commands for creating and populating a table from within mysql? Again, I don't have access to mysqldump. Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb deadlock detection failing?
Marc, please send the printouts to me. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Original Message - From: Marc Slemko [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 25, 2004 8:40 PM Subject: innodb deadlock detection failing? Has anyone seen situations where innodb's deadlock detection fails to detect a deadlock, and things remain deadlocked until the lock wait timeout expires and the server returns a Lock wait timeout exceeded; Try restarting transaction, or have any ideas for why it may be happening? There are no mysql locks (ie. lock table ..., get_lock(), etc.) explicitly being used. I was under the impression that innodb's deadlock detection was guaranteed to always work if no mysql locks were involved. This is running 4.0.15a on Linux, all tables on the server (aside from the mysql database) are innodb. I have some innodb lock monitor dumps, but they aren't overly illuminating, at least to me... all the active transactions are blocking waiting for something, but I can't fully trace who is waiting for who since only 10 locks are printed for each one. -- 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: mysql.user does not reflect privileges granted correctly?
I've verified there is no user called 'ltm' in the mysql.user table. So, yeah, apparently that's a typo in my post. Sorry. So, to re-state my problem: I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql.user shows 'N' in all the *_priv columns for user 'ltmuser'. Bing Bing Du wrote: I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: :: that 'ltm' != 'ltmuser' :-) Or is the example a typo? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user does not reflect privileges granted correctly?
Thanks for the heads-up! Bing Du [EMAIL PROTECTED] wrote: Greetings, I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: *** 1. row *** Host: localhost User: ltmuser Password: 132f058a4532ff99 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 I thought the *_priv fields should all have 'Y' rather than 'N'. Table 'user' contains global level privileges, but you set permissions on the database level. Check 'db' table. Ah, ok. 'db' table looks correct. I also tried 'flush privileges' but it did not help. What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? You must have FILE privilege. I did not realize FILE privilege was not covered by 'grant all'. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problem
I'm having trouble with a query that is supposed to grab a field from each of my tables. and set up a Total line at the bottom of each set. I have: Insurance Company table (insco) Patient table (patient) Procedure table (procrec) Charges table (feeProfiles) Patient Coverage table (coverage) Procedure Code table (cpt) I need to get: Insurance company name (insco.insconame) Patient ID full name built from first and last fields (patient.id, CONCAT(patient.ptlname, , , patient.ptfname) ) Procedure date (procrec.procdt) Procedure Codes (cpt.id) Expected payment total for each procedure and a total for each insurance company ( SUM(feeProfile.expected) ) Currently, I get all of the fields that I need, but some of the totals at the end either don't show up or they are not correct. Here's my current query: (SELECT insco.insconame, pt.id, CONCAT_WS(, , pt.ptlname, pt.ptfname) name, cpt.cptcode , DATE_FORMAT(proc.procdt, %m-%d-%Y) procdt, FORMAT(SUM(fee.expected), 2) sum_expected FROM procrec AS proc, patient AS pt, cpt, feeProfiles AS fee, coverage, insco WHERE proc.procpatient = pt.id AND cpt.id = proc.proccpt AND cpt.id = fee.cpt_ID AND coverage.covpatient = pt.id AND coverage.covinsco = insco.id GROUP BY insco.insconame, CONCAT_WS(, , pt.ptlname, pt.ptfname), DATE_FORMAT(proc.procdt, %m-%d-%Y)) UNION (SELECT DISTINCT insco.insconame, NULL id, NULL name, NULL cptcode, NULL procdt, FORMAT(SUM(fee.expected), 2) sum_expected FROM feeProfiles AS fee, cpt , procrec, patient, coverage, insco WHERE procrec.procpatient=patient.id AND cpt.id = fee.cpt_ID AND procrec.proccpt = cpt.id AND coverage.id = procrec.proccurcovid AND coverage.covinsco = insco.id GROUP BY insco.insconame) ORDER BY insco.insconame Is there an easier way to do this on mysql version 4.18 or should I rework my entire query? Thanks, Craig
need help with a query
Hi. I'm having trouble with a query. I'm wondering if it is possible at all. I'm making a poll for my webpage. I have two tables: Poll and poll_votes Table poll contains data about the poll Poll_Votes contains all the votes. What I want is to pull out data about the poll and if the user has voted or not. I have tried many different queries, but I haven't managed to get it to work. Can someone help me. My poll_votes table has this structure: Poll_id User_id Time I hope someone can help me. - Anders Gjermshus
Help us at MySQL user conference
Hi! I assume most of you know that we will have our annual MySQL User conference at April 14-15 in Orlando. During the conference we will give a lot of information about new MYSQL features that are already implemented in MySQL 4.1 and 5.0 and also about the new features we plan to implement in future MySQL versions. On the conference there will be a lot of MySQL developers around with intimate knowledge of all MySQL server code. I would like you to talk with them and tell us what features of MySQL are important to you. By knowing your needs, we will be able to better satisfy them! For example: - What features you find important and why. - What features you would like us to implement in the near / far future. - Talk with MySQL developers about the problems you have faced with MySQL - Ideas of how to we could do things better. - How to solve some specific problem you are facing with MySQL or some other database. - What you would need from MySQL to be able to move your current application to MySQL - Cool ideas / functions that you would like to see implemented. - New table handlers that you would like to implement / use. If you have a tough problem, put it on your laptop or on a CD/DVD and ask some of the MySQL developers to help you with it during the conference! If you can't come to the conference, please ensure that someone else you know comes and talks with our developers so that we can get your feedback/ideas to improve MySQL. The MySQL developers that will attend the conference, hold talks and be available for questions during the whole conference (and beer/vodka during the evenings) are: - Brian Aker, Director of Architecture (Slashdot Database Architect) UDF, Storage Engines, Apache, replication, Perl, using MySQL under high load. - Sergei Golubchik, Sr Software Engineer MyISAM, Merge Tables, Full Text Search - Lars Thalmann, Software Engineer MySQL Cluster, Replication - Mikael Ronstrom, Sr Software Architect MySQL Cluster, NDB API - Konstantin Osipov, Software Engineer Cursors, Prepared Statements - Peter Gulutzan, Software Architect SQL Standards, Stored Procedures - Alexander Byelkin, Software Engineer Sub-queries, Query Cache, Views - Mark Matthews, Software Dev Manager Connector/J, JDBC, Java - Jim Winstead, Lead Web Developer PHP, Apache, MySQL.com webmaster - Alfredo Kojima, Software Engineer MySQL Adminstrator, Windowmaker, other MySQL GUI tools - Mike Zinner, Software Engineer MySQL Adminstrator, Workbench, other MySQL GUI tools - Peter Zaitsev, Benchmarks Specialist Optimizations, Benchmarks - Alexey Holyfoot Botchkov, Software Developer Embedded Library, GIS Support - Heikki Tuuri, Innodb Author Innodb Storage Engine - David Axmark, Co-Founder MySQL Community - Carsten Pedersen, MySQL certification - Monty Widenius, Founder and CTO Main author of MySQL; Knows most things that are worth knowing about MySQL, MyODBC and some more. You can find more information on our conference including a complete list of sessions, labs, workshops and speakers at: http://www.mysql.com/uc2004 Hope to see a lot of you there! Regards, Monty CTO of MySQL AB PS: The conference is not only for developers. It's perfectly ok to bring your boss with you to get him convinced that MySQL is a safe bet:) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping sql commands
What do you have access to? SSH? ODBC? Port Access? On Thu, 25 Mar 2004, Erich Beyrent wrote: Hi there, I am running a website on a host who has blocked access to mysqldump. Is there any way I can show all the sql commands for creating and populating a table from within mysql? Again, I don't have access to mysqldump. Thanks! -Erich- -- 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]
Sort a Sum
Is there any way to do the following? I get an error whenever I try this (Invalid use of Group function). Select Product, Sum(Qty) From Inventory Group By Product Order By Sum(Qty) DESC; I want the total quantities for all products but I want to see the products with the highest quantities first. Thanks
Re: Sort a Sum
Is there any way to do the following? I get an error whenever I try this (Invalid use of Group function). Select Product, Sum(Qty) From Inventory Group By Product Order By Sum(Qty) DESC; This should work if you assign it an alias...it does for me on 4.018(not exact query) Select Product, Sum(Qty) AS MYSUM From Inventory Group By Product Order By MYSUM DESC; hth jEFF ___ I want the total quantities for all products but I want to see the products with the highest quantities first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select unique records
Hi mysql, i have table 'a' with one field 'name', which contains many dublicated records, like SELECT * FROM a; +---+ | name | +---+ | Peter | | Ivan | | Peter | | Peter | | Dima | | ... | +---+ Now i want to see list of names (not repeated). How can i do it? Tnx, nullevent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select unique records
Use Select * from a Group by [your_field_name] -Original Message- From: nullevent [mailto:[EMAIL PROTECTED] Sent: Friday, March 26, 2004 9:43 AM To: [EMAIL PROTECTED] Subject: select unique records Hi mysql, i have table 'a' with one field 'name', which contains many dublicated records, like SELECT * FROM a; +---+ | name | +---+ | Peter | | Ivan | | Peter | | Peter | | Dima | | ... | +---+ Now i want to see list of names (not repeated). How can i do it? Tnx, nullevent -- 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]
How stable is 4.0.18?
Hello, I am long since using MySQL 3.23.58; and it works perfectly. In fact, it works so well, that I have always been very hesitant to upgrade to the 4.x series. Especially, since 4.x, a year ago or so, was still rather unstable. But I like the new query-cache. So, I wonder, how stable is 4.0.18 really? (compared to 3.23.58). Since I read that I need to upgrade DBD:mysql for all Perl clients as well, I am not too keen to just give it a whirl, only to find out I upgraded to a potential lemon. Counting on an honest assessment (and not the promo rap), how stable is it, really? Can I risk upgrading? Or is it still too flaky? I run a news server on it, and server mail client databases; so I really cannot afford an unstable product. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with a query
sounds like you need a join. Select * from Poll, poll_votes where (Poll.poll_id = poll_votes.poll_id) and (poll_votes.user_ID = WHATEVER); But I'm still a beginner so Respectfully, Ligaya Turmelle Anders Gjermshus [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi. I'm having trouble with a query. I'm wondering if it is possible at all. I'm making a poll for my webpage. I have two tables: Poll and poll_votes Table poll contains data about the poll Poll_Votes contains all the votes. What I want is to pull out data about the poll and if the user has voted or not. I have tried many different queries, but I haven't managed to get it to work. Can someone help me. My poll_votes table has this structure: Poll_id User_id Time I hope someone can help me. - Anders Gjermshus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select unique records
Check out DISTINCT. Respectfully, Ligaya Turmelle Clive Luk [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Use Select * from a Group by [your_field_name] -Original Message- From: nullevent [mailto:[EMAIL PROTECTED] Sent: Friday, March 26, 2004 9:43 AM To: [EMAIL PROTECTED] Subject: select unique records Hi mysql, i have table 'a' with one field 'name', which contains many dublicated records, like SELECT * FROM a; +---+ | name | +---+ | Peter | | Ivan | | Peter | | Peter | | Dima | | ... | +---+ Now i want to see list of names (not repeated). How can i do it? Tnx, nullevent -- 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]
Nested IF statement in 3.23.57
Are nested IF statements valid? Doesn't say anything here: http://www.mysql.com/doc/en/Control_flow_functions.html Trying to get a conditional count using the following statement - but Im receiving an error. I would use a CASE statement but my version doesn't support it. SUM(IF(AdGroups.Status = 'Paused',IF(AdGroupCriteria.Status,Active,1,0),0)) +-+ | version() | +-+ | 3.23.57-Max | +-+
Re: Nested IF statement in 3.23.57
At 18:44 -0500 3/25/04, David Perron wrote: Are nested IF statements valid? Doesn't say anything here: http://www.mysql.com/doc/en/Control_flow_functions.html Yes, you can nest them, but you must do so correctly. :-) Your inner IF() below appears to have four arguments. Trying to get a conditional count using the following statement - but Im receiving an error. I would use a CASE statement but my version doesn't support it. SUM(IF(AdGroups.Status = 'Paused',IF(AdGroupCriteria.Status,Active,1,0),0)) +-+ | version() | +-+ | 3.23.57-Max | +-+ -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
manual replication
hi, all! i was wondering whether anybody has encountered the same situation i am with, and how you deal with it: i've got several mysql servers running in various locations (all connected by [sigh!] on-demand ppp modem connections). every now and then i connec to the servers from our main office, and download a bzip'd mysqldump that crond creates every night, and import them into our main server. this is of course completley idiotic, since i am transferring redundant information like crazy (at MOST 1% of the records change per day). i was looking into using mysql's funky replication for this, but i didn't get an answer on my question whether this multiple master-servers being replicated by a single server can be made to work (normal scenario is one master/multiple slaves). so. if keeping my database-copys here on our main server up-to-date is not possible using replication - how can i accomplish this? i was thinking about switching on bin-logging on the servers, force a FLUSH LOGS (or whatever) whenever i want to update the main-server, and transfer any binlogs that have been created by the FLUSH LOGS to the main server via ftp (or whatever) and import them into the main server's copy of the remote database. but this solution sounds so horrendously inelegant, that i am rather reluctant to even start to implement it without somebody telling me that that actually works. so? anybody know whether that would work? cheers, M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error dropping databases
I am trying to drop several InnoDB and am unable to do so. I login to the command line client and enter drop database [db_name]; and then get this error: ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails Why am I unable to drop these databases? Tom = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(binary?) update log for only one database
hi again! on a related note to my previous question: how can i switch on the creation of a (binary) update log, but only for ONE database of several being on my server? cheers, M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql as cluster service, failover causes broken replication
Matt Sturtz wrote: Yes, the clients (appearently) read to the end of the previous file, and then sit there, while the server is writing to a new file. I was thinking this had to do with the unclean shutdown of MySQL-- perhapps it's something else. It might, but it is a bug anyway. The whole idea of replication is to be able to deal with things like unclean shutdown. First upgrade to 4.0.18. Then if it happens again, use mysqlbinlog -j pos_at_which_the_slave_is_stuck along with od -c to gather some more details ( I suspect a truncated or corrupted binlog event), and send the details to the MySQL developers. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
Tim Cutts wrote: On 25 Mar 2004, at 10:10, Tim Cutts wrote: No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... The version compiled natively on the machine does the same thing (although it uses a little less memory to start with since it's not statically linked). I suppose the next thing to try is the debug version. I've tried compiling the debug version myself without success, so I'll download the debug version from MySQL and try that. Tim: Can you bring your libc to the latest patch level? -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1236 corrupt binlog on master
Arvind Gangal wrote: I have been using the mysqlbinlog to investigate the 'saturn2-bin.051' binlog file.. I have come to pinpoint the problem position in the binlog, but don't know how i can bypass the corrupted segment. This is a bit tricky. Open the binlog in a hex editor, and go to the last good offset (printed in the comments). At offset 9 from there you will find a 4-byte integer in little-endian format. That is the length of the last good event. Add it to the last good offset. Now you are at the corrupted event. This is where the science ends and the black magic begins. Again, at offset 9 you will find what is supposed to be the event length, and at offset 4 you will find 1 byte event type code. The codes are listed in sql/log_event.h in the enum Log_event_type. Figure out where the next good event really starts, and fix up the type to be something innocent (eg. INTVAR_EVENT) + the length so that you will jump to the next event. As a word of caution, you may not find much useful data after the corrupted event, but if it is at the end of file, you can manually put a ROTATE_EVENT to instruct the slave to switch to the next log. However, if this is a practical problem, and not just a study of how things work, I would suggest reporting the bug ( if this happened with the latest version), and resetting the replication. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (binary?) update log for only one database
At 18:55 -0600 3/25/04, Moritz von Schweinitz wrote: hi again! on a related note to my previous question: how can i switch on the creation of a (binary) update log, but only for ONE database of several being on my server? You probably want to enable the binary log with --log-bin, and use the --binlog-do-db option along with it. Databases not named with --binlog-do-db options will be ignored. http://www.mysql.com/doc/en/Binary_log.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How stable is 4.0.18?
Mark wrote: Hello, I am long since using MySQL 3.23.58; and it works perfectly. In fact, it works so well, that I have always been very hesitant to upgrade to the 4.x series. Especially, since 4.x, a year ago or so, was still rather unstable. But I like the new query-cache. So, I wonder, how stable is 4.0.18 really? (compared to 3.23.58). Since I read that I need to upgrade DBD:mysql for all Perl clients as well, I am not too keen to just give it a whirl, only to find out I upgraded to a potential lemon. Counting on an honest assessment (and not the promo rap), how stable is it, really? Can I risk upgrading? Or is it still too flaky? I run a news server on it, and server mail client databases; so I really cannot afford an unstable product. Thanks, - Mark I've been running MySQL-4.0.x since 4.0.2 - which is the first one I could compile. Before that we were using 3.23.something. It was a long time ago. I have had some very limited problems with MySQL's stability in the early 4.0.x days, but I'm pretty sure all of my problems were caused by a failing CPU associated hard system lock-ups which caused file system corruption. In many cases, MySQL recovered very well indeed from these disasters. In some I had to restore from a backup and run the transaction log through. Since I built a real server ( well an Athlon 2000 XP anyway ... it's better than what we had ), I have had NO problems at all. It's been up 24 / 7 without a single crash. This server has used versions 4.0.12 through 4.0.18. I have the query cache enabled, and use a combination of MyISAM and InnoDB tables. The server isn't usually under particularly heavy loads, but we have 35 or so MS Access clients, and sometimes ... well ... they do tend to thrash DB servers in strange ways. The query cache is very good at handling this. Anyway, it's never gone down on decent hardware. Can't say much more than that :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql.user does not reflect privileges granted correctly?
Your grant statement give ltm all privileges on LTM and not on user. By default, ltm is granted usage on user. To see the privileges the user ltm has, try any of these two: select * from db where user='ltmuser'\G; show privileges for [EMAIL PROTECTED] Select from db should show ltm having all privileges on LTM and not user. HTH Babs -Ursprüngliche Nachricht- Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 25. März 2004 18:27 An: [EMAIL PROTECTED] Betreff: Re: mysql.user does not reflect privileges granted correctly? Bing Du [EMAIL PROTECTED] wrote: Greetings, I did: mysql grant all on LTM.* to [EMAIL PROTECTED] identified by 'secret'; But mysql select * from user where user='ltmuser'\G; shows: *** 1. row *** Host: localhost User: ltmuser Password: 132f058a4532ff99 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 I thought the *_priv fields should all have 'Y' rather than 'N'. Table 'user' contains global level privileges, but you set permissions on the database level. Check 'db' table. I also tried 'flush privileges' but it did not help. What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? You must have FILE privilege. I can insert rows one by one into table ltm_enrollment as user 'ltmuser'@'localhost' without any problems. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysql.dll size increased from 188Kb to 916KB
Hello, The size of libmysql.dll binary has increased from 188KB to 916KB from MySQL 4.0 to MySQL 4.1.1. Researching around I came to know that the new libmysql.dll was compiled linking the new character stuff. What do you mean by character stuff? Just curious? Karam __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping sql commands
On Thursday 25 March 2004 03:11 pm, Erich Beyrent wrote: Is there any way I can show all the sql commands for creating and populating a table from within mysql? Here's the first half of your task: SHOW CREATE TABLE MyTableName; --John On Thursday 25 March 2004 03:11 pm, Erich Beyrent wrote: Hi there, I am running a website on a host who has blocked access to mysqldump. Is there any way I can show all the sql commands for creating and populating a table from within mysql? Again, I don't have access to mysqldump. Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort a Sum
Thanks Jeff, After a little more investigation it turns out that the problem only exists when I run the query through a MS Access database attached to MySQL via ODBC. If I run the query in MySQLCC or MySQL-Front it runs just fine. Any ideas for a work around? - Ed [EMAIL PROTECTED] 3/25/04 1:56:10 PM Is there any way to do the following? I get an error whenever I try this (Invalid use of Group function). Select Product, Sum(Qty) From Inventory Group By Product Order By Sum(Qty) DESC; This should work if you assign it an alias...it does for me on 4.018(not exact query) Select Product, Sum(Qty) AS MYSUM From Inventory Group By Product Order By MYSUM DESC; hth jEFF ___ I want the total quantities for all products but I want to see the products with the highest quantities first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user does not reflect privileges granted correctly?
Bing Du [EMAIL PROTECTED] wrote: Thanks for the heads-up! Bing Du [EMAIL PROTECTED] wrote: What I need to do is load data from a file into a table. mysql load data infile '/home/user/LTM/generate_report.pl.ltm_enroll_tmp' into table ltm_enrollment fields terminated by '\t'; ERROR 1045: Access denied for user: 'ltmuser'@'localhost' (Using password: YES) What privileges are needed for doing 'load data infile'? You must have FILE privilege. I did not realize FILE privilege was not covered by 'grant all'. Because FILE is a global level privilege: GRANT FILE ON *.* TO 'ltmuser'@'localhost' IDENTIFIED BY 'secret'; -- 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]
