Re: sql to duplicate records with modified value
thanks, Ray, that worked well (btw, you have a typo, 'Independant' instead of 'Independent') btw2, I have a pdf with some 15,000 names that I would like to display with a search function, I email you later, maybe you can help me with that -- Voytek > Hi Voytek > > > You could try some variation of: > > > INSERT INTO inserttable (user, maildir) > SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/') > as maildir FROM selecttable > [WHERE ..] > > > the where bit is optional of course! > > let me know how you go - hope you are keeping well! > > ray > > At 03:17 PM 27/03/2010, Voytek Eymont wrote: > >> I have Postfix virtual mailboxes in MySQL table like below: >> >> >> I'd like to duplicate all records whilst MODIFYING two fields like so: >> >> >> current record has format like: user 'usern...@domain.tld' maildir >> 'domain.tld/usern...@domain.tld/' >> >> >> add new record that has: user 'username+s...@domain.tld' maildir >> 'domain.tld/usern...@domain.tld/.spam/' >> >> >> so that I'll end up with two record, existing, plus new one >> >> field 'user' - insert '+spam' ahead of '@' field 'maildir' append >> '.spam/' >> >> >> what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu >> (i686) using readline 4.3 >> Server version: 4.1.22-standard >> >> >> mysql> show tables; +---+ >> | Tables_in_postfix | >> +---+ >> | admin | >> | alias | >> | config| >> | domain| >> | domain_admins | >> | fetchmail | >> | log | >> | mailbox | >> | vacation | >> | vacation_notification | >> +---+ >> 10 rows in set (0.00 sec) >> >> >> >> -- >> Voytek >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@tweek.com.au >> > > ( T W E E K ! ) > > > PO Box 15 > Wentworth Falls > NSW Australia 2782 > > > | p:+61 2 4702 6377 (Sydney/Penrith/Blue Mountains) > | p:+61 2 4915 8532 (Newcastle/Hunter) > | f:+61 2 8456 5743 > | m:0414 270 400 > | e:ray at tweek dot com dot au > | w:www dot tweek dot com dot au > > > 'What is more immoral than war?' - Marquis de Sade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql to duplicate records with modified value
> Voytek Eymont wrote: > Are you hoping to do all that you want - copy rows, update rows and > create new rows - in a single SQL statement? Because if that's what you > want, I don't think it's possible. Unless someone has come up with some > new tricks, you can't insert a new record and update an existing one > within the same SQL statement. > > If you want to do it via several SQL statements, each part of what you > want should be possible via different statements in a script where you can > use the script itself to help with the update logic. If you want to do the > updates from the command line only and won't consider a program, I don't > know how to do it. > > I should point out that I'm quite fluent in SQL and have been writing it > for a long time. I haven't used MySQL in a few years now but I'm still on > the mailing list and your question caught my eye. Rhino, thanks a script would be great, if you can help with it, I'd be grateful. I'm not sure where/how to start, the username is a primary index. or do I run a dump, then, work some script over dump, and, re-import ? -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sql to duplicate records with modified value
I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql> show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie installing MySQL/InnoDB on RH 7.1
On Thu, 28 Mar 2002 14:46:01 +0200, Egor Egorov wrote: >You must first install original MySQL (rpm) package and then you can >install MySQK-max RPM. You can read about mysqld-max at: > http://www.mysql.com/doc/m/y/mysqld-max.html > >If you want to use InnoDB tables you should specify InnoDB startup >options in my.cnf file, take a look at: > http://www.mysql.com/doc/I/n/InnoDB_start.html >You can find info about configuration parametrs and example if you >check this link. Egor, thanks ! I obviously misunderstood mysql.com's pages at: Downloads for the Max 3.23 version http://www.mysql.com/downloads/mysql-max-3.23.html MySQL has during the years grown to be a quite big and capable SQL server with a lot of useful extensions that can be enabled at compile time. As not all users will have use for all extensions, we have solved this by providing two different MySQL binaries. The standard MySQL 3.23 binary that is optimized for raw speed and without transactions. The MySQL-Max 3.23 binary which includes support for the most requested features, like support for Berkeley DB (BDB) and/or InnoDB tables. I took it as 'it's a stand alone, no need for non-max' thanks for the help, I'll try again, as I'm rather new to rpm thing, can you pls tell me: if I installed RedHat7.1, and, MySQL that came with RH CD; them I download latest MySQL rpm file: am I supposed to: rpm -UPGRADE OPTION rpm file; or rpm -INSTALL OPTION rpm file ? also, after I install MySQL 3.23 binary rpm, followed by MySQLMAX 3.23 binary rpm, and, edit the my.cnf as you note: do I end up with two MySQLd ? so, I can run either max, or, not max ? does these rpms include mysql client, or what do I need to d/l ? (doing the RH install, it has separate option for client, and mysqld, hence, I'm asking) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie installing MySQL/InnoDB on RH 7.1
I need some newbie help... I've installed RedHat7.1 off RH7.1 CDs, also, installed MySQL that came with it, the installed MySQL did NOT have InnoDB support; I then d/l off mysql.com MySQL-Max-3.23.49a-1.i386.rpm I installed it using: rpm --install MySQL-Max-3.23.49a-1.i386.rpm (I think that's how it's meant to be installed, I'm a newbie) howver, on restart. the 'original CD revison' started, NOT, MAX then, i tried starting MySQL-Max by explicitly calling it, it said: 'you have wrong error files' SO: using webmin, I removed all mysql packages; then, using webmin, installed MySQL-Max-3.23.49a-1.i386.rpm now it tell me, the MySQL-Max-3.23.49a is only an extension: Optional MySQL server binary that supports features like transactional tables. To active this binary, just install this package after the MySQL package. - but, reading on mysql download page, implies, MySQL-Max-3.23.49a-1.i386.rpm is all I need... help so, at this time: accoriding to webmin I have: Packages matching mysql Package Class Description MySQL-Max 3.23.49a-1 Applications/Databases MySQL - server with Berkeley DB and Innodb support perl-DBD-MySQL 1.2215-1 Applications/CPAN A MySQL interface for perl php-mysql 4.0.4pl1-9 Development/Languages A module for PHP applications that use MySQL databases. -- webmin says: MySQL Database Server The MySQL admin program /usr/bin/mysqladmin was not found on your system. Maybe MySQL is not installed, or your module configuration is incorrect. can this MySQL run as a server ? where is a client [root@charly /]# /etc/rc.d/init.d/mysqld start bash: /etc/rc.d/init.d/mysqld: No such file or directory [root@charly /]# clueless, Voytek Eymont http://www.sbt.net.au/links/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Coredump on quickly disconnecting clients
On Sunday 25 March 2001 16:27, you wrote: > Found the problem with your signal 11 with strange traces from inside > LinuxThreads. This was a hard concurrency bug - the problem was that a > memory reference passed to pthread_create was getting invalidated by the > thread that pthread_create was launching before pthread_create would right > to that value. Took me a while to figure this out - I have scrutinized the > entire thread creation mechanism in LinuxThreads suspecting a bad mmap or > munmap somewhere before I realized the problem had nothing to do with > LinuxThreads internal code. > > I believe the problem has existed for as long as MySQL has been around - > back then the machines were just not fast enough to make this condition > happen frequently enough for someone to be able to create a repeatable > case. I have fixed it by extending the critical region. Here is the patch: Fantastic! I take it you won't be wanting the better back trace then? I'll get back to you once I've confirmed that it's fixed it in our case. Thanks a lot for the help. You guys rock. -- Voytek Lapinski [EMAIL PROTECTED] Software Engineer Hotkey Internet ph: 03 9923 3656 mob: 0427 469 891 fax: 03 9923 3388 www.hotkey.net.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connection related SIG 11 crash in 3.23
Ok... I've posted briefly regarding this, but heres a complete bug report.
Yeah I know its kinda long, but I thought it better to include more rather
than less.
We are seeing a problem which results in mysqld dying with a SIGSEGV. The
problem has been confirmed on both these machines
Linux lindt 2.2.18 #2 SMP Mon Feb 26 15:23:32 EST 2001 i686 unknown
Dell PowerEdge 4300 - Dual Pentium 3-500 - 1Gb RAM, 1.6Gb swap
Linux lisa 2.4.1 #7 SMP Fri Feb 16 15:21:45 EST 2001 i686 unknown
IBM Rackmount Server 325 - Dual Pentium Pro-200 - 512Mb RAM, 1Gb Swap
And using mysqld versions 3.23.32,3.23.33 and 3.23.35. To confirm that the
problem isn't related to libraries, compilers or configuration specific to our
build environment, I have also tested the standard linux-i386 binary distro of
3.23.35, and the problem also exists there.
The error message takes the following form:
mysqld got signal 11;
The manual section 'Debugging a MySQL server' tells you how to use a
stack trace and/or the core file to produce a readable backtrace that may
help in finding out why mysqld died.
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, ebp=0xb108, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x813fefa
0x813f6dd
0x8081690
0x8081b14
0x808127e
0x8145e4b
0x8048111
New value of ebp failed sanity check, terminating backtrace!
Naturally with different values for the stack backtrace depending on version
(the addresses look similarly spaced though, so it's probably the same thing).
The problem appears to be related to connections which occur at roughly the
same time. I have written a script which reliably causes it to occur and have
included it below. It operates by forking off lots of processes all of which
connect and disconnect to the database repeatedly until it goes down. In most
cases, the database will crash within seconds, although rarely it seems
to take significantly longer (several minutes). The problem I am referring to
is associated with local UNIX domain socket connections, but when I briefly
tested connecting over TCP/IP it (or something else similar) still occurs in
that it eventually stops accepting connections on that port (although the
database is still running and generates no errors). This may be something
else entirely though, and I have not investigated that at all.
While this case seems somewhat artificial, we were seeing the same crashes
under normal operation of our application which seemed to be associated with
the activation of a process that frequently will fork of 6 new processes, all
of which attempt to connect into the database immediately after starting.
The test script follows:
--START SCRIPT--
#!/usr/bin/perl -w
use DBI;
for(my $i=0;$i<100;$i++) {
my $fork_ret=fork;
if (not defined($fork_ret)) {
die("$! fork failed\n");
} elsif ($fork_ret == 0) {
while(1) {
my$
dbh=DBI->connect('DBI:mysql:database=test','test','test',{PrintError=>1,RaiseError=>0});
exit unless (defined($dbh));
$dbh->disconnect();
}
}
}
my $start=time();
my $wait_ret;
do {
$wait_ret=wait;
} until $wait_ret == -1;
print ((time()-$start)."s\n");
--END OF SCRIPT---
The [mysql] section of our my.cnf is:
--START CONFIG--
[mysqld]
port=
socket = /tmp/mysql.sock
skip-locking
set-variable= key_buffer=32M
set-variable= max_allowed_packet=1M
set-variable= thread_stack=128K
set-variable= tmp_table_size=8M
set-variable= max_connections=200
--END CONFIG--
But I have tried more conservative values for key_buffer (down to 4M),
thread_stack(64K) and tmp_table_size(2M). The problem also seems to occur
with values of max_connections anywhere from 20 to 1 (Yeah I know 1 is
silly with LinuxThreads, but I thought I'd do it to see what happens).
When invoking mysql, many options have been used, but the most conservative
settings we tried were
/usr/local/mysql-3.23.35-bindist/bin/mysqld --datadir=/data --user=root
The problem can be made to take longer to happen by a few things, but of
course this is rather subjective. It is harder to trigger when using the
3.23.35 binary distro, trying on the faster machine and using larger values
for max_connections. We have been unable to make it go away entirely though.
Eventually, it will always go down.
that's it... I'm happy to provide any more info, as we would really love to
see this fixed.
Oh yeah, another weird thing that we noticed while testing this that may or
may not be related is that once in a while when running the test script, a
user would appear to be logged in as their password instead of their login
name. No investigation was done on this issue.
--
Voytek Lapinski [
core dumps
I'm trying to diagnose a segmentation fault we're getting in mysql so I can send a slightly decent bug report about it, and would like to get a core dump. I CANNOT get it to dump core. I'm using the --core-file option. I've made sure my ulimit to coredump size is set big enough. It just won't do it. It's a 3.23.35 database (also testing 3.23.32 which exhibits the same problem). Anyone help? I'll be posting more details regarding the actual problem soonish once I reckon I know what it is. (It seems to be some sort of non thread safe business in the code that handles connections that can happen when you get a lot of simultaneous connections at around the same time) -- Voytek Lapinski [EMAIL PROTECTED] Software Engineer Hotkey Internet ph: 03 9923 3656 mob: 0427 469 891 fax: 03 9923 3388 www.hotkey.net.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
