Currently I have a query that is able to return (doing a left join):
'A', 'B', 'E'
'A', 'B', 'F'
'C', 'D', NULL
But I need this:
'A', 'B', 'E,F'
'C', 'D', NULL
Can SQL help me in any way here ? or do I have to do it by programming ?
If you use MySQL 4.1 or later take a look at
Hi SQL-Users
Is it possible to select only every second record from a record set?
I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4,
6, 8, ...
Can this be done with LIMIT?
Thank you!
Greetings, Martin
Hello,
Is it possible to select only every second record from a record set?
I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2,
4,
6, 8, ...
Can this be done with LIMIT?
Besides the obvious why? ...
A table, by itself, does not have every second record, as it has
[snip]
Is it possible to select only every second record from a record set?
I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2,
4,
6, 8, ...
Can this be done with LIMIT?
[/snip]
Not LIMIT, but you can use MOD, especially with an auto-increment field
(id in this case is
i have come to realise that temporary tables are only persistent for 30
secs, is there a way to extend that ? My applications is attempting to
store data before and entry is made and then insert the data from the
temporary table after the entry is inserted then drop the temp table.
This should
[snip]
Jay Blanchard wrote:
[snip]
Is it possible to select only every second record from a record set?
I should select the record-number 1, 3, 5, 7, 9, ... or record-number
2,
4,
6, 8, ...
Can this be done with LIMIT?
[/snip]
Not LIMIT, but you can use MOD, especially with an
Hello Artem,
thanks for your answer.
For selective replication I would check startup
options --replicate-do-* and --replicate-wild-*.
I'm using replicate-do-db on the client but the problem is, that the
master sends the sql-statements of ALL databases to the repl-client and
the
I'm remotely administering a fedora 2 snort box via ssh.
running snort with
snort -c /etc.snort/snort.conf is fine - that is everything is logged
to tables and shows in acid.
But if i leave it running for over about a minute it buggers up. I
lose my ssh and acid/apache service. I then have to get
Hello.
It is an unusual behaviour for InnoDB tables to create .MYD files.
Execute 'show create table' statement on your table to see the
type of storage engine. What output does the following statement
produce:
show variables like 'have_innodb';
Grumm, Carsten [EMAIL PROTECTED]
Hello.
Misterious 'unauthenticated user' problems appears very seldom in
lists and bugs database with different versions. My opinion, that
it is somehow related to the networking. Do you use --skip-name-resolve option?
Have you compiled the MySQL server manually? Send us your config file
Hello.
What's in the error log? Usually it can be found in the data directory,
if another location wasn't specified in the config file or the command line
options. MySQL server was restared by the mysqld_safe script, through the
crash of mysqld process.
Alfredo Reynoso [EMAIL
Hello.
Use:
set autocommit=0;
or begin a transaction before executing 'select ... lock in share mode'.
Commit the tramsaction to release lock.
Mojtaba Faridzad [EMAIL PROTECTED] wrote:
Hi,
I am trying to learn more about InnoDB to convert MyISAM to InnoDB.
according to MySQL
Hello,
I apologize for a possible simple question, but I am having trouble with the
below code.
I have three simple tables. In short, menu_group has an id, that is
referenced in the menu_group_rel. When a user deletes an id from the
menu_group, I wanted the entry in menu_group_rel (data_id)
At 08:30 AM 1/27/2005, electroteque wrote:
i have come to realise that temporary tables are only persistent for 30
secs, is there a way to extend that ? My applications is attempting to
store data before and entry is made and then insert the data from the
temporary table after the entry is
Hi
I am building a transactional sistem with mysql/innodb tables i would like to
make sure my steps are rigth, i am using php on the webserver to manipulate
these mysql tables.
---
Also i try to use select ... read
Hello Scott,
Make sure your tables are InnoDB type:
CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB;
If you have default MyISAM tables, it won't work because they don't support
foreign keys.
-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Thursday,
Hello Lutz,
I was not aware of this behavior of the master server. Maybe somebody with more
insight can explain. If the traffic volume is so important I would turn on
compression on the master-slave connection to reduce network traffic. I think
it is slave_compressed_protocol=1 option in the
This is the error log (comes from the file hostname.com.err in the data
directory). The part where mysqld is restarted comes at the end, I've cutted
most of the restarts because the only diference is the hour, and because
there were a lot of restarts.
One more thing, I think this mail would
Thanks,
I updated my script and all looks good. But now I get an error when tryng to
issue this command.
Any ideas?
Thanks,
Scott
mysql ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(d
ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE;
ERROR 1005: Can't create table
Have you tried perror yet?
[EMAIL PROTECTED] (ping-300) 120 perror 150
MySQL error: 150 = Foreign key constraint is incorrectly formed
Cheers,
--V
Scott Purcell wrote:
Thanks,
I updated my script and all looks good. But now I get an error when tryng to
issue this command.
Any ideas?
Thanks,
Try this.
The second set ... select gives you what you want.
However, the group by may interfere with the rest of your logic. You also
don't really need the mod(@a,2) in the result set, just in the having.
mysql set @a:=0;
Query OK, 0 rows affected (0.00 sec)
mysql select @a:[EMAIL
[snip]
mysql set @a:=0;
Query OK, 0 rows affected (0.00 sec)
mysql select @a:[EMAIL PROTECTED],mod(@a,2),ordr_ID, poft_Sub_Month from
er_poft
group by 3,4 having mod(@a,2) = 0 limit 5;
+--+---+-++
| @a:[EMAIL PROTECTED] | mod(@a,2) | ordr_ID |
Thanks for the info on the command line util. I am new and was unaware of it.
But anyway, I have gone through the docs many more times, and I cannot figure
out where my problem is. Of course the error is a foreign key problem, but I do
not see where. I even am basically using a textbook example
Hi
I have an aplication wich is opening to many connections even i am using
persistent connectins and closing every connection i do open
I have created indexes in all the fields i supose they are needed
I would like to know if i can and how can i measure where indexes are needed or
Problem somewhat solved:
I am somewhat thrown by what I stumbled across, but it has to do with the _
underscore character. If I run the same below script without the _, it works
fine. But as soon as I put in the underscore _ it fails with the error.
This sounds pretty insane? They are
Having repeatable problems doing restores, 4.1.8 and 4.1.9 both do the
same error
Is there some setting I'm missing?
mysqldump -u username-pPassword --all-databases --quote-names
/intranet/backup/backup.sql
E:\intranet\backupmysql -u xotech -pDaredevil22 -f backup.sql
ERROR at line 2153:
Consider the simple case where we have a single master and a single slave.
Ideally, we would redirect all writes to the master server only, and reads
on the master
AND the slave.
Is it possible to redirect UPDATE, DELETE, INSERT queries only on the master
server
automatically, and SELECT* on
No this is not a mysql option directly. Your going to have to change
your database API level to write only to the master and read only from
the slave. The only thing that mysql will do is enforce read only on a
slave via GRANTS and write only on a master.
DVP
Dathan Vance Pattishall
Hi,
You have an error in the statement. See below. If you remove the comma after
primary key def everything should work.
[skip]
CODE:
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'spurcell' WITH GRANT OPTION;
CREATE DATABASE builder;
use builder;
CREATE TABLE
Database size and records are find. But your table scanning. Look at
Handler_read_rnd_next 1018281500
that indicates a table scan. This mean your not using your indexes effectivly
or the tables are not set up with the proper indexes.
Perform show full processlist and or enable
José Pablo Ezequiel Fernández [EMAIL PROTECTED] wrote on 01/26/2005
03:49:50 PM:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
After the good comments I've got on this mailing list, I think I have
the
structure more or less complete. In some cases I follow the comments, in
some
Is there a way I can select from a text field and limit the amount of text I
get back in my query? For example, if I have an articleText field of type
TEXT, and the article contains 4000 words, is there a way to select that
text with a limit of 200 words, or should this kind of logic go in my php
[snip]
Is there a way I can select from a text field and limit the amount of
text I
get back in my query? For example, if I have an articleText field of
type
TEXT, and the article contains 4000 words, is there a way to select that
text with a limit of 200 words, or should this kind of logic go in
Does anyone know the GA release date for Mysql 5.0 ?
Thanks!
Nupur
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ângelo M. Rigo [EMAIL PROTECTED] wrote on 01/27/2005 03:04:15
PM:
Hi
I have an aplication wich is opening to many connections even i am
using persistent connectins and closing every connection i do open
I have created indexes in all the fields i supose they are needed
I would like
Greetings
Has anyone been able to successfully upgrade from 4.0.22, for example,
to 4.1.9 using the windows installer and server instance wizard? For me,
every time the wizard hits the 'Apply security settings' part of the
config it freezes. I am now trying a manual install but wanted to know
This could be a case where your dump files are not split in such a way so
that they honor the max_packet_length setting of the recieving server.
Make sure you tell mysql dump what the maximum size of an INSERT statement
will be for the server you want to read the dump into or it will put all
I'm running a query that pulls information from about six different tables
in a DB. I'd like to be able to do a fulltext search on fields in several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
There is a new bot spreading on the Internet that targets insecure MySQL
installations on Windows, as reported at
http://isc.sans.org/diary.php?isc=a508f4a185755af19ea8bd45444a570b.
I am now going to quote the advisory:
--
Infection Method
The bot
cristopher pierson ewing [EMAIL PROTECTED] wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a
Trying to install MySql 4.1 on XP using the automated installer. Have tried to
install both the windows essentials and windows X86 . Tried doing typical and
custom versions. Also used the Instance Configuration both detail and standard.
When I get to the dialog box with the execute button I
Hello, MySQL gurus,
I have a non-unique key of 2 columns, the 1st is a varchar(15),
2nd is a int(10) unsigned. But when I 'explain' a query that uses this
key, the key_len is only 15 with the key name shown up in the 'key'
column of this table correctly though. According to the manual,
Shawn,
Thanks for the reply.
Here's the output of SHOW CREATE TABLE for one of the tables in
question:
CREATE TABLE `tblcourseextrainfo` (
`course_id` varchar(6) NOT NULL default '',
`course_description` text,
`course_intended_audience` text,
`course_keywords` text,
PRIMARY KEY
Hello,
I have a replication setup on to linux boxes (debian woody, kernel 2.4.21-xfs,
mysql 4.1.7-standard official intel-compiler binary from mysql.com).
master:~# mysqladmin status
Uptime: 464848 Threads: 10 Questions: 296385136 Slow queries: 1752 Opens:
2629 Flush tables: 1 Open
Our production databases here are really growing and getting to be rather
big. The question on our minds is; when is a database or table just too big?
Our InnoDB datafile was 116GB last I checked, and I know we've got a few
20GB+ databases on there, but my real concern is over the table sizes. I
Hi,
I'm really stuck here. I'm using version 3.23.58. I tried to create a
database foo using phpAdmin(logged in as root) and got:
ERROR 1006: Can't create database 'foo'. (errno: 13)
I checked [EMAIL PROTECTED] in the user table and it has all priviledges.
Went to linux and logged in as root,
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten
good results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
Does anyone know the point that the MySQL Administrator can't report on
table sizes?
Misao:
I do not use MySQL Adminstrator, so I do not know at what point it is unable to
correctly report the size of a table. But if there exists a table that it cannot
correctly report the size of, it is a bug
Hi there,
For some reason, despite entering my passwords in ~/.my.cnf, I still have
to enter a password on the command line to login to mysql. I have tried
this with each of the accounts I have created and all return the same
error:
$ mysql -u sebyte
ERROR 1045: Access denied for user: '[EMAIL
[snip]
I'm using version 3.23.58. I tried to create a database foo using
phpAdmin(logged in as root) and got:
ERROR 1006: Can't create database 'foo'. (errno: 13)
...
drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql
[/snip]
perror 13
Error code 13: Permission denied
File permissions look ok
Our database server has 4Gigs of ram on it, and we have a hard time of
figuring out the true InnoDB settings on how to use that ram up.
I ended up just increasing the ram used until it just wouldn't run anymore,
then backed up and used that. We plan on adding another 4Gigs of ram, total
of 8GB.
ERROR 1006: Can't create database 'foo'. (errno: 13)
errno 13 is 'Permission denied'
drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql
locate the directory that contains the 'test' and 'mysql' databases.
This will be the directory that you do not have permission to
write in for mysql. This is
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Je ado Januaro 27 2005 08:40, Jigal van Hemert skribis:
Currently I have a query that is able to return (doing a left join):
'A', 'B', 'E'
'A', 'B', 'F'
'C', 'D', NULL
But I need this:
'A', 'B', 'E,F'
'C', 'D', NULL
Can SQL help me in
Hi List,
Today the hard drive from my server where MySQL was running crashed. I have
a dump from yesterday night but I would like to save the work for today
before the crash. I saved the full directory of the MySQL
(/usr/local/mysql).
Is my data inside the frm file or in another file? How can I
Hi everyone,
I am having a problem starting up mysql.
I am unable to run the mysql_install_db routine. When I run it I keep getting
the error - unable to write to /tmp/root/...
Originally I got the error unable to write to /tmp but that has since changed
to the first error.
I'm not a newbee
I looked around and didn't see documentation of MySQL
error codes. I did find a short list of INNODB codes
but nothing comprehensive. Is there such a page?
Thanks,
Tripp
__
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
[snip]
Is my data inside the frm file or in another file? How can I access and
retrieve my data?
[/snip]
The data is not in the frm files. If you are using myisam tables the data is
in the myd files and the indexes are in the myi files. Assuming the files
didn't get badly damaged, you should be
[snip]
I looked around and didn't see documentation of MySQL error codes. I did
find a short list of INNODB codes but nothing comprehensive. Is there such a
page?
[/snip]
You can use perror to find out want a mysql errno means.
http://dev.mysql.com/doc/mysql/en/perror.html
---
Tom Crimmins
http://www.be-known-online.com/mysql/
(B
(BBest regards
(B
(BNils Valentin
(B
(B
(B [snip]
(B I looked around and didn't see documentation of MySQL error codes. I did
(B find a short list of INNODB codes but nothing comprehensive. Is there such
(B a
(B page?
(B [/snip]
(B
(B You can
Hi Emmett,
(B
(BPlease try http://www.be-known-online.com/mysql/
(B
(BNote that this error list is for the Linux version (parts differs for
(Banother OS). More error descriptions can be found in the header files.
(B(forgot currently which ones). If you search the forum for error codes and
[snip]
I looked around and didn't see documentation of MySQL error codes. I did
find a short list of INNODB codes but nothing comprehensive. Is there such a
page?
[/snip]
OS error codes :
http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html
Server error messages :
There is a bot active on the internet that is infecting Windows machines
running MySQL Server:
http://isc.sans.org/diary.php?isc=a508f4a185755af19ea8bd45444a570b
An alert with background information is already available on:
http://dev.mysql.com/tech-resources/articles/security_alert.html
The
Has anyone ever had a problem with Alteon load balancers leaving the
MySQL connections half open? After about a minute of heavy use the
Alteon has completely DoS'd our MySQL servers. I know we must be doing
something wrong...just not sure what. Any help is greatly appreciated!
Best Regards,
Jason
I had similar problem after incomplete removal MySQL 4.0.x which left dead
service named MySQL that points to non-existing path c:\mysql. I changed the
windows service name for MySQL 4.1 to MySQL41, and it works fine. Also you can
try to execute mysqld from command line to see any extra
Jason J. W. Williams wrote:
Has anyone ever had a problem with Alteon load balancers leaving the
MySQL connections half open? After about a minute of heavy use the
Alteon has completely DoS'd our MySQL servers. I know we must be doing
something wrong...just not sure what. Any help is greatly
65 matches
Mail list logo
