Re: What is mysql_refresh() ?
Paul DuBois wrote: It's not obsolete, just undocumented. Okay, thanks. It's documented in MySQL++ now. :) (It doesn't say more than that bug DB entry, so I doubt you want a corresponding MySQL doc patch.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is mysql_refresh() ?
At 21:10 -0600 5/19/05, Warren Young wrote: I'm the maintainer for MySQL++ (a C++ wrapper for the MySQL C API) and while digging through the code I found a reference to mysql_refresh(), apparently a C API function. I can't find documentation for this anywhere. What does it do? If it's obsolete, I'd like to know that, too. I'll remove it from MySQL++ in that case. It's not obsolete, just undocumented. There is, in fact, a bug report noting that fact: http://bugs.mysql.com/bug.php?id=9816 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is mysql_refresh() ?
I'm the maintainer for MySQL++ (a C++ wrapper for the MySQL C API) and while digging through the code I found a reference to mysql_refresh(), apparently a C API function. I can't find documentation for this anywhere. What does it do? If it's obsolete, I'd like to know that, too. I'll remove it from MySQL++ in that case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
password is lose
Hello all, I'm having a bit of trouble:. Welcome to phpMyAdmin 2.5.0-rc1 Error MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) how can I find my password? Thanks as always! -Stone.wang
Tunning MySQL formulas
Is there a good place/book to lookup formulas for tunning mysql ? What size should the table_cache be ? I have 2,770 tables What size should the key_buffer be ? I am only using 38 MB out of 265 MB, yet my Index Length reported by mysql-administrator is 2.72 G Total number of records are 61 Million Rows. Most queries will be select on primary key, or group by primary key. Any help is appreciated. -Steve More -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 Crashing on Mandrake 10.1
Hmmm, I downloaded source and compiled, and had an instant segfault. Rolled back to 4.1.11. I assumed it was something funky w/ my compile, but after reading all the above posts ... ??? Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Donny Simonton wrote: I'm not sure but I know when I installed it yesterday via RPM it kept core dumping and restarting. Glad I still had 4.1.11 available. Donny Donny, Douglas, Something goofy went on with the production of those RPMs for 4.1.12 that didn't show up during release QA, so once we found out that users were having issues with them, we pulled them off the website until we can figure out what went wrong. For now, the tarballs should be okay if you can use those. It seems that it only is related to how the RPMs were packaged and/or post-processed. Regards, -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCjR8RtvXNTca6JD8RAprmAJ9Qmcp2dRI5ETK46eLB7NtwotyW+QCghBty GzP1vQKkPG3lOetGqk1Dp5E= =O7OB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 Crashing on Mandrake 10.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Donny Simonton wrote: > I'm not sure but I know when I installed it yesterday via RPM it kept core > dumping and restarting. Glad I still had 4.1.11 available. > > Donny Donny, Douglas, Something goofy went on with the production of those RPMs for 4.1.12 that didn't show up during release QA, so once we found out that users were having issues with them, we pulled them off the website until we can figure out what went wrong. For now, the tarballs should be okay if you can use those. It seems that it only is related to how the RPMs were packaged and/or post-processed. Regards, -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCjR8RtvXNTca6JD8RAprmAJ9Qmcp2dRI5ETK46eLB7NtwotyW+QCghBty GzP1vQKkPG3lOetGqk1Dp5E= =O7OB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.12 Crashing on Mandrake 10.1
I'm not sure but I know when I installed it yesterday via RPM it kept core dumping and restarting. Glad I still had 4.1.11 available. Donny > -Original Message- > From: Douglas K. Fischer [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 19, 2005 1:18 PM > To: mysql@lists.mysql.com > Subject: Re: 4.1.12 Crashing on Mandrake 10.1 > > Mark Hughes wrote: > > > > >A lot of the RPM's for 4.1.12 seems to have been removed from the > >download page so I presume there's a known problem: > > > >http://dev.mysql.com/downloads/mysql/4.1.html > > > > > I noticed this when I just went to download 4.1.12. What's going on? Is > 4.1.12 unstable or is this strictly a build/package issue? Any ETA on > the return of the RPMs? > > Cheers, > > Doug > > -- > 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: Efficient select/insert
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = "INSERT into $table2"; # dynamic name with $user_id as root
$sql .= sprintf <<'EOF', join(',', @array);
I see.
I suppose this produced quite a few grins.
Happy to oblige. :)
Thanks again Eamon,
--Jon
(col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF
You could simplify it like this:
$sql = sprintf <<'EOF', $table2, join(',', @array);
INSERT INTO %s (col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF
SOme explanation of the details:
- join(',', @array)
join concats a list to a string, joined by some character.
See "perldoc -f join".
- sprintf
sprintf behaves like its C counterpart and allows
interpolation of numbers and strings.
See "perldoc -f sprintf".
- <<'EOF'
That's a "here document". We put it in single quotes so
perl-ish stuff like '$' and '@' are not interpolated.
See http://www.perlmeme.org/howtos/interpolation.html
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
In article <[EMAIL PROTECTED]>,
"Eamon Daly" <[EMAIL PROTECTED]> writes:
> my $sql = sprintf <<'EOF', join(',', @array);
> SELECT col2, col3, col4
> FROM table1
> WHERE col1 IN (%s)
> EOF
> my $sth = $dbh->prepare($sql);
> $sth->execute() or die $sth->errstr();
This code is susceptible for an SQL injection attack. I'd use
something like the following instead:
my $sql = q{
SELECT col2, col3, col4
FROM table1
WHERE col1 IN (%s)
};
my $sth = $dbh->prepare(sprintf $sql,
join ',', map { $dbh->quote($_) } @array);
$sth->execute() or die $sth->errstr();
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
- Original Message -
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, May 19, 2005 1:17 PM
Subject: Re: Efficient select/insert
my $sql = sprintf <<'EOF', join(',', @array);
SELECT col2, col3, col4
FROM table1
WHERE col1 IN (%s)
EOF
my $sth = $dbh->prepare($sql);
$sth->execute() or die $sth->errstr();
Eamon Daly
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = "INSERT into $table2"; # dynamic name with $user_id as root
$sql .= sprintf <<'EOF', join(',', @array);
(col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, May 19, 2005 11:52 AM
Subject: Re: Efficient select/insert
> This would be even faster if you could concatenate all of the
> elements
of
> @array into a single list then you could say:
>
> #My PERL skills are non existent so you need to write this part.
> #I am assuming that @array is holding a list of string values.
>
> foreach my $element (@array) {
> @araylist += "'" + $element + "',";
> }
> @arraylist = left(@arraylist, length(@arraylist) -1)
>
> #that's to remove the trailing comma at the end of the list
>
> my $sql = "select col2, col3, col4 from table1
> where col1 IN (?)";
> my $sth = $dbh->prepare($sql);
> $sth->execute(@arraylist) or die $sth->errstr();
>
I've finally gotten around to this. It doesn't appear to be
possible using a placeholder.
$list # a string of an unknown number of CS numeric primary keys.
$sql = "insert into table2
(col2, col3, ...)
select col2, col3, ...
from table1
where id in (?)";
$sth = $dbh->prepare($sql);
$sth->execute($list) || die ...
This inserts only the first item from $list.
$sth->execute(2,4) complains about mismatched number of bind variables.
An array (@list) does the same thing (only worse:)
where id in ($list)" returns an SQL syntax error.
Should I just fall back or am I missing something?
foreach $item (@list) {
$sql = "insert into table2 ...
Thanks,
Jon
--
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: 4.1.12 Crashing on Mandrake 10.1
Mark Hughes wrote: A lot of the RPM's for 4.1.12 seems to have been removed from the download page so I presume there's a known problem: http://dev.mysql.com/downloads/mysql/4.1.html I noticed this when I just went to download 4.1.12. What's going on? Is 4.1.12 unstable or is this strictly a build/package issue? Any ETA on the return of the RPMs? Cheers, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Troubleshooting FullText Slowness
Dathan,
Thanks for the suggestion - I was under the impression that 4GB is the limit
for 32bit machines. The server is running Redhat Enterprise Linux 3.0 ES for
EM64T and MySQL has also been compiled for EM64T.
I'd already tried running 'table opitmize' and that didn't seem to help,
table repair doesn't seem to help performance either. :(
I should also mention that this is version 4.1.10
Thanks,
Dan
On 5/19/05, Dathan Pattishall <[EMAIL PROTECTED]> wrote:
>
> Wow, your going pass the 2 GB barrier on a 32 bit server. I would not do
> that. Reduce your key buffer to 1700 or you will eventually crash.
>
>
> Then type repair table PC1_Text quick, or ALTER TABLE PC1_TEXT
> ENGINE=myISAM. This helps with rebuilding full text indexes.
>
>
> DVP
>
> Dathan Vance Pattishall http://www.friendster.com
>
>
>
> > -Original Message-
> > From: Dan Salzer [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, May 19, 2005 7:48 AM
> > To: mysql@lists.mysql.com
> > Subject: Troubleshooting FullText Slowness
> >
> > Hello all,
> > I'm having a bit of trouble with a full-text query being
> > slow. At first I thought it was a problem with a join, then I
> > thought it was a problem with a sort - but I've boiled down
> > the query and it seems like plain-old slowness.
> > This is the table:
> >
> > CREATE TABLE `PC1_Text` (
> > `AssetID` int(11) NOT NULL default '0',
> > `Content` text NOT NULL,
> > PRIMARY KEY (`AssetID`),
> > FULLTEXT KEY `Content` (`Content`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> > And here is its status:
> > Name: PC1_Text
> > Engine: MyISAM
> > Version: 9
> > Row_format: Dynamic
> > Rows: 5906
> > Avg_row_length: 15849
> > Data_length: 93608372
> > Max_data_length: 4294967295
> > Index_length: 49875968
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2005-04-09 12:25:41
> > Update_time: 2005-05-19 10:22:14
> > Check_time: 2005-05-19 09:58:00
> > Collation: latin1_swedish_ci
> > Checksum: NULL
> > Create_options:
> > Comment:
> > The table has only 5900 rows of text and I'm trying to use
> > the full-text index to find hits on a search term using:
> > SELECT SQL_NO_CACHE AssetID FROM PC1_Text as FT WHERE
> > MATCH(Content) AGAINST('+"after dinner" ' IN BOOLEAN MODE);
> > This query takes about 1.6 seconds to execute. More general
> > queries take even longer. Performing an explain on the query
> > shows exactly what I would
> > expect:
> > ++-+---+--+---+---
> > --+-+--+--+-+
> > | id | select_type | table | type | possible_keys | key |
> > key_len | ref
> > | |
> > rows | Extra |
> > ++-+---+--+---+---
> > --+-+--+--+-+
> > | 1 | SIMPLE | FT | fulltext | Content | Content | 0 | | 1 |
> > Using where
> > | |
> > ++-+---+--+---+---
> > --+-+--+--+-+
> > I've even taken the time to run:
> > LOAD INDEX INTO CACHE PC1_Text;
> > And it doesn't make a difference.
> > All this leads me to believe I am dealing with a system
> > configuration issue. The server is a Dual Xeon 2.8 EM64T, it
> > has 4GB of memory and 15K Drives. And I am currently using
> > --memlock to ensure that mysql stays in real memory. My
> > config looks like the following:
> > [mysqld]
> > key_buffer_size=2500M
> > tmp_table_size=128M;
> > max_heap_table_size=128M;
> > max_allowed_packet = 1M
> > table_cache = 512
> > sort_buffer_size = 2M
> > read_buffer_size = 2M
> > read_rnd_buffer_size = 8M
> > thread_concurrency = 8
> > thread_cache = 8
> > query_cache_size = 64M
> > max_connections=1
> > ft_min_word_len=3
> > Anyone have any idea what I might try to increase the
> > performance of this query? I'm quite certain it should be
> > able to do better than this.
> > Thanks as always!
> > -Dan
> >
>
MYSQL y Northon Personal Firewall
Error: C:\ARCHIV~1\MySQL\MYSQLS~1.1\bin>mysql -hlocalhost -uroot -p Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) I`have a Northon Personal Firewall, and when run de config server wizard give this error: "The security settings could not be applied to the databese because de connetction has failded with de following error: Error Nro 2003: Can`t connect to MySQL server on `localhost´(10061) If a personal firewal running on you machine, please make sure you have opened de TCP port 3306 for conections" HOW DOES THAT EN "Personal Northon Firewall 2005" ??? Thanks Fernando.
Re: Efficient select/insert
my $sql = sprintf <<'EOF', join(',', @array);
SELECT col2, col3, col4
FROM table1
WHERE col1 IN (%s)
EOF
my $sth = $dbh->prepare($sql);
$sth->execute() or die $sth->errstr();
Eamon Daly
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, May 19, 2005 11:52 AM
Subject: Re: Efficient select/insert
> This would be even faster if you could concatenate all of the elements
of
> @array into a single list then you could say:
>
> #My PERL skills are non existent so you need to write this part.
> #I am assuming that @array is holding a list of string values.
>
> foreach my $element (@array) {
> @araylist += "'" + $element + "',";
> }
> @arraylist = left(@arraylist, length(@arraylist) -1)
>
> #that's to remove the trailing comma at the end of the list
>
> my $sql = "select col2, col3, col4 from table1
> where col1 IN (?)";
> my $sth = $dbh->prepare($sql);
> $sth->execute(@arraylist) or die $sth->errstr();
>
I've finally gotten around to this. It doesn't appear to be
possible using a placeholder.
$list # a string of an unknown number of CS numeric primary keys.
$sql = "insert into table2
(col2, col3, ...)
select col2, col3, ...
from table1
where id in (?)";
$sth = $dbh->prepare($sql);
$sth->execute($list) || die ...
This inserts only the first item from $list.
$sth->execute(2,4) complains about mismatched number of bind variables.
An array (@list) does the same thing (only worse:)
where id in ($list)" returns an SQL syntax error.
Should I just fall back or am I missing something?
foreach $item (@list) {
$sql = "insert into table2 ...
Thanks,
Jon
--
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: Troubleshooting FullText Slowness
Wow, your going pass the 2 GB barrier on a 32 bit server. I would not do
that. Reduce your key buffer to 1700 or you will eventually crash.
Then type repair table PC1_Text quick, or ALTER TABLE PC1_TEXT
ENGINE=myISAM. This helps with rebuilding full text indexes.
DVP
Dathan Vance Pattishall http://www.friendster.com
> -Original Message-
> From: Dan Salzer [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 19, 2005 7:48 AM
> To: mysql@lists.mysql.com
> Subject: Troubleshooting FullText Slowness
>
> Hello all,
> I'm having a bit of trouble with a full-text query being
> slow. At first I thought it was a problem with a join, then I
> thought it was a problem with a sort - but I've boiled down
> the query and it seems like plain-old slowness.
> This is the table:
>
> CREATE TABLE `PC1_Text` (
> `AssetID` int(11) NOT NULL default '0',
> `Content` text NOT NULL,
> PRIMARY KEY (`AssetID`),
> FULLTEXT KEY `Content` (`Content`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> And here is its status:
> Name: PC1_Text
> Engine: MyISAM
> Version: 9
> Row_format: Dynamic
> Rows: 5906
> Avg_row_length: 15849
> Data_length: 93608372
> Max_data_length: 4294967295
> Index_length: 49875968
> Data_free: 0
> Auto_increment: NULL
> Create_time: 2005-04-09 12:25:41
> Update_time: 2005-05-19 10:22:14
> Check_time: 2005-05-19 09:58:00
> Collation: latin1_swedish_ci
> Checksum: NULL
> Create_options:
> Comment:
> The table has only 5900 rows of text and I'm trying to use
> the full-text index to find hits on a search term using:
> SELECT SQL_NO_CACHE AssetID FROM PC1_Text as FT WHERE
> MATCH(Content) AGAINST('+"after dinner" ' IN BOOLEAN MODE);
> This query takes about 1.6 seconds to execute. More general
> queries take even longer. Performing an explain on the query
> shows exactly what I would
> expect:
> ++-+---+--+---+---
> --+-+--+--+-+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref
> | |
> rows | Extra |
> ++-+---+--+---+---
> --+-+--+--+-+
> | 1 | SIMPLE | FT | fulltext | Content | Content | 0 | | 1 |
> Using where
> | |
> ++-+---+--+---+---
> --+-+--+--+-+
> I've even taken the time to run:
> LOAD INDEX INTO CACHE PC1_Text;
> And it doesn't make a difference.
> All this leads me to believe I am dealing with a system
> configuration issue. The server is a Dual Xeon 2.8 EM64T, it
> has 4GB of memory and 15K Drives. And I am currently using
> --memlock to ensure that mysql stays in real memory. My
> config looks like the following:
> [mysqld]
> key_buffer_size=2500M
> tmp_table_size=128M;
> max_heap_table_size=128M;
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> read_rnd_buffer_size = 8M
> thread_concurrency = 8
> thread_cache = 8
> query_cache_size = 64M
> max_connections=1
> ft_min_word_len=3
> Anyone have any idea what I might try to increase the
> performance of this query? I'm quite certain it should be
> able to do better than this.
> Thanks as always!
> -Dan
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 Crashing on Mandrake 10.1
On 5/18/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > Hello. > > > > > > > As a footnote to this the 4.1.12 build is significantly smaller, is > > > this expected? > > > > Check the md5 signature of your packages to be sure in its' origin. > > Search in the other rpm's for Linux x86. If you'll be unable to find > > mysqld.sym.gz for you distribution - switch to the debug version of > > the server. > A lot of the RPM's for 4.1.12 seems to have been removed from the download page so I presume there's a known problem: http://dev.mysql.com/downloads/mysql/4.1.html The binary download works fine. Encouraged me to try out the Intel Compiler builds anyway :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
> This would be even faster if you could concatenate all of the elements
of
> @array into a single list then you could say:
>
> #My PERL skills are non existent so you need to write this part.
> #I am assuming that @array is holding a list of string values.
>
> foreach my $element (@array) {
> @araylist += "'" + $element + "',";
> }
> @arraylist = left(@arraylist, length(@arraylist) -1)
>
> #that's to remove the trailing comma at the end of the list
>
> my $sql = "select col2, col3, col4 from table1
> where col1 IN (?)";
> my $sth = $dbh->prepare($sql);
> $sth->execute(@arraylist) or die $sth->errstr();
>
I've finally gotten around to this. It doesn't appear to be
possible using a placeholder.
$list # a string of an unknown number of CS numeric primary keys.
$sql = "insert into table2
(col2, col3, ...)
select col2, col3, ...
from table1
where id in (?)";
$sth = $dbh->prepare($sql);
$sth->execute($list) || die ...
This inserts only the first item from $list.
$sth->execute(2,4) complains about mismatched number of bind variables.
An array (@list) does the same thing (only worse:)
where id in ($list)" returns an SQL syntax error.
Should I just fall back or am I missing something?
foreach $item (@list) {
$sql = "insert into table2 ...
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columns
On 19.05., [EMAIL PROTECTED] wrote: > If you add another command, > mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key update > whentime = NOW(); > The right ID will be used. Yes, if I insert an new value then the ID column gets incremented. But if I try to insert an existing value (in the second column), the LAST_INSERT_ID() contains a random value (the next auto_increment value going to be used?) afterwards. So I can't rely in retrieving LAST_INSERT_ID(). > Since last_insert_id() has a connection scope, it's better for you to use : > select max(id) from bla1; That's not what I want. I need the ID of the row just inserted (for referencing it from another table). If I select the max(id), then one the hand someone might have added another row in the meantime and on the other hand is still don't get the ID value in the ON DUPLICATE KEY UPDATE case ... Sven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columns
Hi, If you add another command, mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key update whentime = NOW(); The right ID will be used. Since last_insert_id() has a connection scope, it's better for you to use : select max(id) from bla1; Mathias Selon Sven Paulus <[EMAIL PROTECTED]>: > Hi, > > I'd like to insert string values into a table. If I add a new string, I want > to get back the value of the AUTO_INCREMENT column. If the string already > exists in the table, I'd like to get the AUTO_INCREMENT value of the existing > entry. > > I thought this might be possible using INSERT ... ON DUPLICATE KEY UPDATE > ..., but LAST_INSERT_ID() seems to be unusable in this case. > > Example: > > mysql> CREATE TABLE `bla1` ( > -> `id` int(10) unsigned NOT NULL auto_increment, > -> `value` varchar(255) default NULL, > -> `whentime` timestamp(14) NOT NULL, > -> PRIMARY KEY (`id`), > -> UNIQUE KEY `value` (`value`) > -> ) TYPE=MyISAM > -> ; > Query OK, 0 rows affected, 1 warning (0.10 sec) > > mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update > whentime = NOW(); > Query OK, 1 row affected (0.08 sec) > > mysql> select LAST_INSERT_ID(); > +--+ > | LAST_INSERT_ID() | > +--+ > |1 | > +--+ > 1 row in set (0.04 sec) > > mysql> insert into bla1 values (NULL, "Hallo", NULL) on duplicate key update > whentime = NOW(); > Query OK, 1 row affected (0.01 sec) > > mysql> select LAST_INSERT_ID(); > +--+ > | LAST_INSERT_ID() | > +--+ > |2 | > +--+ > 1 row in set (0.00 sec) > > mysql> select * from bla1; > ++---+-+ > | id | value | whentime| > ++---+-+ > | 1 | Cello | 2005-05-18 17:14:53 | > | 2 | Hallo | 2005-05-18 17:15:38 | > ++---+-+ > 2 rows in set (0.00 sec) > > mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update > whentime = NOW(); > Query OK, 2 rows affected (0.00 sec) > > mysql> select LAST_INSERT_ID(); > +--+ > | LAST_INSERT_ID() | > +--+ > |3 | > +--+ > 1 row in set (0.00 sec) > > mysql> select * from bla1; > ++---+-+ > | id | value | whentime| > ++---+-+ > | 1 | Cello | 2005-05-18 17:15:58 | > | 2 | Hallo | 2005-05-18 17:15:38 | > ++---+-+ > 2 rows in set (0.00 sec) > > > I expected that the last INSERT clause would set the LAST_INSERT_ID() to 1. > But of course the MySQL manual says "The value of LAST_INSERT_ID() is not > changed if you update the AUTO_INCREMENT column of a row with a non-magic > value (that is, a value that is not NULL and not 0)." Ok, I didn't even > update the id column at all. If I use id=NULL in the UPDATE clause, the id > column is changed to 0 and the LAST_INSERT_ID() still contains the wrong > value ... > > Now I'm curious if it's possible at all to use LAST_INSERT_ID() together > with INSERT ... ON DUPLICATE KEY UPDATE ... - how can I determine if a > row was inserted or updated and if I can trust the LAST_INSERT_ID() > value? > > Something like >SET @myid:=0 >... ON DUPLICATE KEY UPDATE @myid:=id > doesn't work, since I can't assign user variables in this part. > > So, is there a way to INSERT an entry if neccessary and always get the id of > the entry? > > Sven > > -- > 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]
Troubleshooting FullText Slowness
Hello all,
I'm having a bit of trouble with a full-text query being slow. At first I
thought it was a problem with a join, then I thought it was a problem with a
sort - but I've boiled down the query and it seems like plain-old slowness.
This is the table:
CREATE TABLE `PC1_Text` (
`AssetID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`AssetID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And here is its status:
Name: PC1_Text
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5906
Avg_row_length: 15849
Data_length: 93608372
Max_data_length: 4294967295
Index_length: 49875968
Data_free: 0
Auto_increment: NULL
Create_time: 2005-04-09 12:25:41
Update_time: 2005-05-19 10:22:14
Check_time: 2005-05-19 09:58:00
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
The table has only 5900 rows of text and I'm trying to use the full-text
index to find hits on a search term using:
SELECT SQL_NO_CACHE AssetID FROM PC1_Text as FT WHERE MATCH(Content)
AGAINST('+"after dinner" ' IN BOOLEAN MODE);
This query takes about 1.6 seconds to execute. More general queries take
even longer. Performing an explain on the query shows exactly what I would
expect:
++-+---+--+---+-+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+---+--+---+-+-+--+--+-+
| 1 | SIMPLE | FT | fulltext | Content | Content | 0 | | 1 | Using where |
++-+---+--+---+-+-+--+--+-+
I've even taken the time to run:
LOAD INDEX INTO CACHE PC1_Text;
And it doesn't make a difference.
All this leads me to believe I am dealing with a system configuration
issue. The server is a Dual Xeon 2.8 EM64T, it has 4GB of memory and 15K
Drives. And I am currently using --memlock to ensure that mysql stays in
real memory. My config looks like the following:
[mysqld]
key_buffer_size=2500M
tmp_table_size=128M;
max_heap_table_size=128M;
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_concurrency = 8
thread_cache = 8
query_cache_size = 64M
max_connections=1
ft_min_word_len=3
Anyone have any idea what I might try to increase the performance of this
query? I'm quite certain it should be able to do better than this.
Thanks as always!
-Dan
ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columns
Hi, I'd like to insert string values into a table. If I add a new string, I want to get back the value of the AUTO_INCREMENT column. If the string already exists in the table, I'd like to get the AUTO_INCREMENT value of the existing entry. I thought this might be possible using INSERT ... ON DUPLICATE KEY UPDATE ..., but LAST_INSERT_ID() seems to be unusable in this case. Example: mysql> CREATE TABLE `bla1` ( -> `id` int(10) unsigned NOT NULL auto_increment, -> `value` varchar(255) default NULL, -> `whentime` timestamp(14) NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY `value` (`value`) -> ) TYPE=MyISAM -> ; Query OK, 0 rows affected, 1 warning (0.10 sec) mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update whentime = NOW(); Query OK, 1 row affected (0.08 sec) mysql> select LAST_INSERT_ID(); +--+ | LAST_INSERT_ID() | +--+ |1 | +--+ 1 row in set (0.04 sec) mysql> insert into bla1 values (NULL, "Hallo", NULL) on duplicate key update whentime = NOW(); Query OK, 1 row affected (0.01 sec) mysql> select LAST_INSERT_ID(); +--+ | LAST_INSERT_ID() | +--+ |2 | +--+ 1 row in set (0.00 sec) mysql> select * from bla1; ++---+-+ | id | value | whentime| ++---+-+ | 1 | Cello | 2005-05-18 17:14:53 | | 2 | Hallo | 2005-05-18 17:15:38 | ++---+-+ 2 rows in set (0.00 sec) mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update whentime = NOW(); Query OK, 2 rows affected (0.00 sec) mysql> select LAST_INSERT_ID(); +--+ | LAST_INSERT_ID() | +--+ |3 | +--+ 1 row in set (0.00 sec) mysql> select * from bla1; ++---+-+ | id | value | whentime| ++---+-+ | 1 | Cello | 2005-05-18 17:15:58 | | 2 | Hallo | 2005-05-18 17:15:38 | ++---+-+ 2 rows in set (0.00 sec) I expected that the last INSERT clause would set the LAST_INSERT_ID() to 1. But of course the MySQL manual says "The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0)." Ok, I didn't even update the id column at all. If I use id=NULL in the UPDATE clause, the id column is changed to 0 and the LAST_INSERT_ID() still contains the wrong value ... Now I'm curious if it's possible at all to use LAST_INSERT_ID() together with INSERT ... ON DUPLICATE KEY UPDATE ... - how can I determine if a row was inserted or updated and if I can trust the LAST_INSERT_ID() value? Something like SET @myid:=0 ... ON DUPLICATE KEY UPDATE @myid:=id doesn't work, since I can't assign user variables in this part. So, is there a way to INSERT an entry if neccessary and always get the id of the entry? Sven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restoring mySQL dump
[snip] `user_id` mediumint(8) unsigned NOT NULL default '0', `group_id` mediumint(8) unsigned NOT NULL default '0', `quota_type` smallint(2) NOT NULL default '0', `quota_limit_id` mediumint(8) unsigned NOT NULL default '0', KEY `quota_type` (`quota_type`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; [/snip] [snip from earlier e-mail] The mySQL version is: 4.0.22 [/snip] Quote from http://dev.mysql.com/doc/mysql/en/create-table.html "As of MySQL 4.1, character column definitions can include a CHARACTER SET attribute to specify the character set and, optionally, a collation for the column. For details, see Chapter 10, Character Set Support. CHARSET is a synonym for CHARACTER SET. CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);" CHARSET is not supported in your version of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not a rpm package?
On Thursday 19 May 2005 14:36, Bing Du typed: > > The problem is exactly as the problem says, you're trying to install > > something > > that isn't an RPM package. Try: > > > > file MySQL-server-5.0.4-0.i386.rpm > > > > It'll tell you what the file you've downloaded actually is. Either it's a > > corrupted rpm, or you've downloaded a link to the package, instead of the > > package itself. > > > > HTH > > Daniel > > Thanks for the help. > > $ file MySQL-server-5.0.4-0.i386.rpm > MySQL-server-5.0.4-0.i386.rpm: data > > Is that correct? No. $ file xmltex-2118-13.noarch.rpm xmltex-2118-13.noarch.rpm: RPM v3 bin xmltex-2118-13 Re-download the file, and check to see if there are MD5 checksums that will tell you if it was corrupted. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring mySQL dump
Hello, None of these lines describe the problem that you are having with syntax. Can you give us 5 lines either side of the line that includes 'DEFAULT CHARSET=latin1'? Yes, here it is: `user_id` mediumint(8) unsigned NOT NULL default '0', `group_id` mediumint(8) unsigned NOT NULL default '0', `quota_type` smallint(2) NOT NULL default '0', `quota_limit_id` mediumint(8) unsigned NOT NULL default '0', KEY `quota_type` (`quota_type`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `db1_attach_quota` -- /*!4 ALTER TABLE `db1_attach_quota` DISABLE KEYS */; LOCK TABLES `db1_attach_quota` WRITE; UNLOCK TABLES; /*!4 ALTER TABLE `db1_attach_quota` ENABLE KEYS */; -- -- Table structure for table `db1_attachments` -- Thank you very much indeed. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not a rpm package?
> The problem is exactly as the problem says, you're trying to install > something > that isn't an RPM package. Try: > > file MySQL-server-5.0.4-0.i386.rpm > > It'll tell you what the file you've downloaded actually is. Either it's a > corrupted rpm, or you've downloaded a link to the package, instead of the > package itself. > > HTH > Daniel Thanks for the help. $ file MySQL-server-5.0.4-0.i386.rpm MySQL-server-5.0.4-0.i386.rpm: data Is that correct? Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: substituting a certain word in all the tinytext rows in a table using SQL command
symbulos wrote: Dear friend, Is it possible to substitute, using SQL command, a certain word with another word in all the field in all the rows (tinytext) within a certain table? Thanks in advance look for REPLACE : http://dev.mysql.com/doc/mysql/en/string-functions.html UPDATE . FROM SET field=REPLACE(field,'plop','foo'); -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to Mysql server during query using a group by clause and sub query
On May 19, 2005, at 12:51 AM, Dan Rossi wrote: Hi there, I am having issues with this funny error message. I am trying to do a sub query and then a group by clause on a date. However i keep getting this annoying message for some reason. Happens via terminal aswell as my sql gui. Here is the query I am trying to do select (select count(discrepancy_originID) FROM report_log WHERE discrepancy_originID=1) as total FROM report_log GROUP BY MONTH (tx_date) let me know, if i take the sub query out its ok ? Is your server crashing during the query? Check the .err file which should be in the data directory for restarts. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restoring mySQL dump
[snip] Yes, here are the first 10 lines: -- MySQL dump 10.9 -- -- Host: localhostDatabase: account_databasename -- -- -- Server version 4.1.8-standard /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; ERROR 1064 at line 20: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 7 [/snip] Can we see the first 10 lines of backupname.sql? Then maybe we can determine what the syntax error is that is NEAR line 7 [/snip] None of these lines describe the problem that you are having with syntax. Can you give us 5 lines either side of the line that includes 'DEFAULT CHARSET=latin1'? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring mySQL dump
Hello, Yes, here are the first 10 lines: -- MySQL dump 10.9 -- -- Host: localhostDatabase: account_databasename -- -- -- Server version 4.1.8-standard /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; Thanks very much. Best regards, Adrian. [snip] mysql -u root -p dbname < backupname.sql And getting: ERROR 1064 at line 20: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 7 [/snip] Can we see the first 10 lines of backupname.sql? Then maybe we can determine what the syntax error is that is NEAR line 7 -- 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: Restoring mySQL dump
[snip] mysql -u root -p dbname < backupname.sql And getting: ERROR 1064 at line 20: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 7 [/snip] Can we see the first 10 lines of backupname.sql? Then maybe we can determine what the syntax error is that is NEAR line 7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring mySQL dump
Hi, I am now using the following: mysql -u root -p dbname < backupname.sql And getting: ERROR 1064 at line 20: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 7 The mySQL version is: 4.0.22 And yes I can login to the mySQL command line with the root password. Any suggestions would be much appreciated. Thank you very much. Best regards. Can you sign -in as root user i.e. Administrator? Using: mysql -u username -p password? Dwayne Hottinger wrote: That is correct. The password for mysql root is probably not the same as the system root. ddh Quoting Adrian Cooper <[EMAIL PROTECTED]>: Hello, I need to restore a mySQL dump file but cannot find the right syntax. I have root access and have used: mysql -u root -p account_databasename < backupname But I am getting: Access denied for user '[EMAIL PROTECTED]' (Using password: YES) I have also removed the root password and got: Access denied for user '[EMAIL PROTECTED]' (Using password: No) What is the correct syntax please? Also, can I create a new database and restore the dump file to it or do I need to use the same database name as before? Thank you very much indeed. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Dwayne Hottinger Network Administrator Harrisonburg City Public Schools -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- 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: substituting a certain word in all the tinytext rows in a table using SQL command
symbulos wrote: Is it possible to substitute, using SQL command, a certain word with another word in all the field in all the rows (tinytext) within a certain table? - see REPLACE here: http://dev.mysql.com/doc/mysql/en/string-functions.html - ian -- +---+ | Ian Sales Database Administrator | | | | "All your database are belong to us" | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
substituting a certain word in all the tinytext rows in a table using SQL command
Dear friend, Is it possible to substitute, using SQL command, a certain word with another word in all the field in all the rows (tinytext) within a certain table? Thanks in advance -- symbulos - ethical services for your organisation website www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 Crashing on Mandrake 10.1
Hello. > As a footnote to this the 4.1.12 build is significantly smaller, is > this expected? Check the md5 signature of your packages to be sure in its' origin. Search in the other rpm's for Linux x86. If you'll be unable to find mysqld.sym.gz for you distribution - switch to the debug version of the server. Mark Hughes <[EMAIL PROTECTED]> wrote: > On 5/18/05, Mark Hughes <[EMAIL PROTECTED]> wrote: >> Hi, >>=20 >> I just upgraded from 4.1.11 (which worked fine, and stil does if I >> downgrade) to 4.1.12 and the first query crashes the server. This >> query is executed via DBD::mysql, when run via the mysql client it >> works OK though. I presume this must be some library conflict but has >> anyone got any idea what? >>=20 > > As a footnote to this the 4.1.12 build is significantly smaller, is > this expected? > > 15504128 MySQL-server-4.1.11-0.i386.rpm > 12111588 MySQL-server-4.1.12-0.i386.rpm > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remote connection problem
Hello. Your server listens only on a single interface. Options could be given in different ways, not only from configuration file. If you want MySQL server to listen on every interface you should find where the bind option is given and remove it. See: http://dev.mysql.com/doc/mysql/en/program-options.html Andy McHargue <[EMAIL PROTECTED]> wrote: > [-- text/plain, encoding 7bit, charset: us-ascii, 29 lines --] > > I'm having trouble connecting remotely from Server A (local) to Server B > (remote). Both Linux. > > From Server A, I'm issuing this command > mysql -h [domain.com] -u [user] -p > > And I get > ERROR 2003: Can't connect to MySQL server on 'domain.com' (110) > I assume this is a time out. > > 1. The user on Server B is set up with all privileges. > 2. Server B's my.cnf has the line "bind-address=127.0.0.1" but it is > commented out with # > 3. Server B's skip_networking is OFF. > 4. A netstat -tl on Server B gives the line > tcp0 0 domain.com:3306 *:* LISTEN > > I notice that on a different server, I get this > tcp0 0 *:mysql > *:* LISTEN > > and there's no line containing 3306. > > What's with that? > > 5. for troubleshooting's sake, ssh [remote-ip] -p 3306 times out. > telnet is unavailable. > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remote connection problem
Hello. After checking that MySQL server listens on the interface to which you are connecting, solve network problems. If you're able to connect from one host to another it doesn't mean that the opposite is true. Andy McHargue <[EMAIL PROTECTED]> wrote: > Here's some more data on this, if anyone can help. > > --I can connect the other way around ... i.e. I can connect from B to > A. So there's no general connectivity problem. > --did an nmap on B, > $ nmap -sT -T Polite -p3306 xx.com > > Starting nmap V. 2.54BETA22 ( www.insecure.org/nmap/ ) > Interesting ports on s191.n33.xx.com (66.84.xx.xx): > Port State Service > 3306/tcp filteredmysql > > nmap on B indicates 'open' rather than 'filtered' ... how can i change? > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: access 3.23 tables on 5.0?
Hello. It looks like you have a symptom of a downward-incompatible table format change. Use mysqldump. See: http://dev.mysql.com/doc/mysql/en/downgrading.html "Bing Du" <[EMAIL PROTECTED]> wrote: > We moved a bunch of *.frm, *.MYD and *.MYI from 5.0 to 3.23. Now after > successfully connecting to 3.23, it shows: > > Didn't find any fields in table 'person' > Didn't find any fields in table '...' > > > mysql> desc person; > ERROR 1033: Incorrect information in file: './LTM/person.frm' > > Is there anyway to make these tables created on 5.0 to be accessible on 3= > .23? > > Thanks in advance for any help. > > Bing > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting to 4.1 server
Hello. You may switch to the debug version of libmysqlclient and research the problem. See: http://dev.mysql.com/doc/mysql/en/debugging-client.html > Yes, the mysql.exe is the same version - I installed version 4.1.10 of > the server (and binaries) from the Windows executable > myself, about two months ago. > > I've tried setting MYSQL_SECURE_AUTH to both true and false, to see if > it solves the problem, but it doesn't. > > Not sure what else I can try. Any other ideas? > > Gleb Paharenko wrote: > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not a rpm package?
On Wednesday 18 May 2005 21:27, Bing Du wrote: > I downloaded MySQL-server-5.0.4-0.i386.rpm. When I did 'rpm -i > MySQL-server-5.0.4-0.i386.rpm', the following message returned: > > MySQL-server-5.0.4-0.i386.rpm: not an rpm package (or package manifest): > > What's the problem? > > Bing The problem is exactly as the problem says, you're trying to install something that isn't an RPM package. Try: file MySQL-server-5.0.4-0.i386.rpm It'll tell you what the file you've downloaded actually is. Either it's a corrupted rpm, or you've downloaded a link to the package, instead of the package itself. HTH Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Email Column
with '@' added to the second param : update emails set t=concat(substring_index(t,'@',1),'@mydomain.com'); :o) Selon [EMAIL PROTECTED]: > Hi , > you can. see this : > mysql> select * from emails; > +---+ > | t | > +---+ > | [EMAIL PROTECTED] | > | [EMAIL PROTECTED] | > | [EMAIL PROTECTED] | > +---+ > 3 rows in set (0.00 sec) > > mysql> update emails set t=concat(substring_index(t,'@',1),'mydomain.com'); > Query OK, 3 rows affected (0.05 sec) > Rows matched: 3 Changed: 3 Warnings: 0 > > mysql> select * from emails; > +---+ > | t | > +---+ > | name1mydomain.com | > | name1mydomain.com | > | name1mydomain.com | > +---+ > 3 rows in set (0.00 sec) > > Mathias > > Selon shaun thornburgh <[EMAIL PROTECTED]>: > > > Hi, > > > > Is it possible to update all the domain names for an email column? I want > to > > change everyones email address to my domain for a test site, so can I > change > > the email address column such that everything after @ is changed to > > mydomain.com? > > > > Thanks for your advice > > > > > > > > -- > > 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: Update Email Column
Hi , you can. see this : mysql> select * from emails; +---+ | t | +---+ | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | +---+ 3 rows in set (0.00 sec) mysql> update emails set t=concat(substring_index(t,'@',1),'mydomain.com'); Query OK, 3 rows affected (0.05 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from emails; +---+ | t | +---+ | name1mydomain.com | | name1mydomain.com | | name1mydomain.com | +---+ 3 rows in set (0.00 sec) Mathias Selon shaun thornburgh <[EMAIL PROTECTED]>: > Hi, > > Is it possible to update all the domain names for an email column? I want to > change everyones email address to my domain for a test site, so can I change > the email address column such that everything after @ is changed to > mydomain.com? > > Thanks for your advice > > > > -- > 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: Update Email Column
shaun thornburgh wrote: Is it possible to update all the domain names for an email column? I want to change everyones email address to my domain for a test site, so can I change the email address column such that everything after @ is changed to mydomain.com? - see REPLACE here: http://dev.mysql.com/doc/mysql/en/string-functions.html - ian -- +---+ | Ian Sales Database Administrator | | | | "All your database are belong to us" | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update Email Column
Hi, Is it possible to update all the domain names for an email column? I want to change everyones email address to my domain for a test site, so can I change the email address column such that everything after @ is changed to mydomain.com? Thanks for your advice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.015 Vs MySQL 4.1.x
I have a decent number of tables (around 100 ~ 150 ish), some of them are really chunky. The total db is close to 40 gb. Cheers Manoj - Original Message - From: "Rafal Kedziorski" <[EMAIL PROTECTED]> To: Cc: <[EMAIL PROTECTED]> Sent: Thursday, May 19, 2005 4:57 PM Subject: Re: MySQL 4.015 Vs MySQL 4.1.x > Hi, > > I'm thinking too about switching to MySQL 4.1.x and if it better to user > file per table. > > How much tables and data have you in your db? And which performance you have? > > > Best Regards, > Rafal > > > At 09:53 19.05.2005, ManojW wrote: > >Dear All, > > I am using MySQL 4.0.15 on a Linux (RH9) for the past 18 months. All my > >tables are InnoDB and tend to be extremely large in nature (running in > >couple of millions, actually the ibdata files now stand at 40GB's). > >Typically, the database has fewer connections but those who connect use the > >database pretty heavily (basically for mathematical modelling purpose etc). > >Basically I am very happy with MySQL/InnoDB and I think the developers did > >an amazing at that. > > > > With MySQL 4.1.x being stable for a while, I am wondering if I should > >upgrade from MySQL 4.0.15 to MySQL 4.1.x? > > > > The pros of the migration are :1) Ability to use prepared statements > >(avaliable from 4.1.x onwards) 2) Ability to perform nested queries. > > > > On the cons side, the biggest factor is .Migration ;-). > > > > Based on your experiences, Could you kindly help me in making a better > >decision? Should I go ahead with the migration? If I do the migration, will > >it have any *significant impact* on the following factors: > > > > 1) Performance (Speed of execution etc) > > 2) Efficiency in terms of database size > > 3) New features/bug fixes that can potentially made your life easier > >after the migration. > > > > I guess the performance factors is of my biggest concern. Everything > >equal, I don't want the speed to slow down If I go ahead with the migration > >;-) > > > > Your kind inputs please!!. Thank you in advance. > > > >Cheers > > > >Manoj > > > > -- > 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 4.015 Vs MySQL 4.1.x
Hi, I'm thinking too about switching to MySQL 4.1.x and if it better to user file per table. How much tables and data have you in your db? And which performance you have? Best Regards, Rafal At 09:53 19.05.2005, ManojW wrote: Dear All, I am using MySQL 4.0.15 on a Linux (RH9) for the past 18 months. All my tables are InnoDB and tend to be extremely large in nature (running in couple of millions, actually the ibdata files now stand at 40GB's). Typically, the database has fewer connections but those who connect use the database pretty heavily (basically for mathematical modelling purpose etc). Basically I am very happy with MySQL/InnoDB and I think the developers did an amazing at that. With MySQL 4.1.x being stable for a while, I am wondering if I should upgrade from MySQL 4.0.15 to MySQL 4.1.x? The pros of the migration are :1) Ability to use prepared statements (avaliable from 4.1.x onwards) 2) Ability to perform nested queries. On the cons side, the biggest factor is .Migration ;-). Based on your experiences, Could you kindly help me in making a better decision? Should I go ahead with the migration? If I do the migration, will it have any *significant impact* on the following factors: 1) Performance (Speed of execution etc) 2) Efficiency in terms of database size 3) New features/bug fixes that can potentially made your life easier after the migration. I guess the performance factors is of my biggest concern. Everything equal, I don't want the speed to slow down If I go ahead with the migration ;-) Your kind inputs please!!. Thank you in advance. Cheers Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.015 Vs MySQL 4.1.x
Dear All, I am using MySQL 4.0.15 on a Linux (RH9) for the past 18 months. All my tables are InnoDB and tend to be extremely large in nature (running in couple of millions, actually the ibdata files now stand at 40GB's). Typically, the database has fewer connections but those who connect use the database pretty heavily (basically for mathematical modelling purpose etc). Basically I am very happy with MySQL/InnoDB and I think the developers did an amazing at that. With MySQL 4.1.x being stable for a while, I am wondering if I should upgrade from MySQL 4.0.15 to MySQL 4.1.x? The pros of the migration are :1) Ability to use prepared statements (avaliable from 4.1.x onwards) 2) Ability to perform nested queries. On the cons side, the biggest factor is .Migration ;-). Based on your experiences, Could you kindly help me in making a better decision? Should I go ahead with the migration? If I do the migration, will it have any *significant impact* on the following factors: 1) Performance (Speed of execution etc) 2) Efficiency in terms of database size 3) New features/bug fixes that can potentially made your life easier after the migration. I guess the performance factors is of my biggest concern. Everything equal, I don't want the speed to slow down If I go ahead with the migration ;-) Your kind inputs please!!. Thank you in advance. Cheers Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different TIMESTAMP columns
Hi,
You have two way to set two default timestamp columns.
FIRST :
just use a variable :
mysql> select @t:=current_timestamp;
+---+
| @t:=current_timestamp |
+---+
| 2005-05-19 08:58:56 |
+---+
1 row in set (0.01 sec)
mysql> insert into bookmark
(bookmarkname,url,folder_id,last_scanned,last_notified)
values('t','t','t',@t,@t);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from bookmark;
+-+--+-+---+-+-+
| bookmark_id | bookmarkname | url | folder_id | last_scanned|
last_notified |
+-+--+-+---+-+-+
| 1 | t| t | 0 | 2005-05-19 08:55:54 |
2005-05-19 08:55:54 |
| 2 | t| t | 0 | 2005-05-19 08:58:56 |
2005-05-19 08:58:56 |
+-+--+-+---+-+-+
Even if the default is not set for your columns.
SECOND :
-
We can use the fact that timestamp can't be NULL. This is the trick :
mysql> CREATE TABLE bookmark2 (
-> bookmark_id INTEGER NOT NULL AUTO_INCREMENT,
-> bookmarkname VARCHAR (80) NOT NULL,
-> url VARCHAR (150) NOT NULL,
-> folder_id INTEGER NOT NULL,
-> last_scanned timestamp NOT NULL default '-00-00',
-> last_notified timestamp default current_timestamp on update
current_timestamp,
-> PRIMARY KEY (bookmark_id)
-> ) TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> insert into bookmark2 (bookmarkname,url,folder_id) values('t','t','t');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from bookmark2;
+-+--+-+---+-+-+
| bookmark_id | bookmarkname | url | folder_id | last_scanned|
last_notified |
+-+--+-+---+-+-+
| 1 | t| t | 0 | -00-00 00:00:00 |
2005-05-19 09:13:58 |
+-+--+-+---+-+-+
1 row in set (0.00 sec)
mysql> insert into bookmark2 (bookmarkname,url,folder_id,last_scanned)
values('t','t','t',NULL);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql>select * from bookmark2;
+-+--+-+---+-+-+
| bookmark_id | bookmarkname | url | folder_id | last_scanned|
last_notified |
+-+--+-+---+-+-+
| 1 | t| t | 0 | -00-00 00:00:00 |
2005-05-19 09:13:58 |
| 2 | t| t | 0 | 2005-05-19 09:16:16 |
2005-05-19 09:16:16 |
+-+--+-+---+-+-+
2 rows in set (0.00 sec)
So if you want a for insert, and a for update two columns, INSERT NULL in the
first, and put current_timestamp for the updated as default.
Mathias
Selon Lieven De Keyzer <[EMAIL PROTECTED]>:
> mysql> CREATE TABLE bookmark (
> -> bookmark_id INTEGER NOT NULL AUTO_INCREMENT,
> -> bookmarkname VARCHAR (80) NOT NULL,
> -> url VARCHAR (150) NOT NULL,
> -> folder_id INTEGER NOT NULL,
> -> last_scanned DATETIME DEFAULT NOW(),
> -> last_notified DATETIME DEFAULT NOW(),
> -> PRIMARY KEY (bookmark_id),
> -> FOREIGN KEY (folder_id) REFERENCES folder(folder_id) ON DELETE
> CASCADE) TYPE = InnoDB;
>
> ERROR 1067 (42000): Invalid default value for 'last_scanned'
>
> >From: Simon Garner <[EMAIL PROTECTED]>
> >To: mysql@lists.mysql.com
> >Subject: Re: Different TIMESTAMP columns
> >Date: Thu, 19 May 2005 09:52:02 +1200
> >
> >Lieven De Keyzer wrote:
> >>
> >>But how do I initialize the 2 datetime fields? With no DEFAULT, their
> >>values are -00-00 00:00:00. Just what I wanted to avoid with TIMESTAMP
> >>
> >
> >Set their value to NOW() if you want the current date/time.
> >
> >-Simon
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
> >http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB
Hi, I have a question: which way is better for storing data in InnoDB with MySQL 4.1.x? File per table or one big file? How is the performance with file per table? Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install mysql question
Dear there, I have some problems when installing mysql under rpm. Hope some people can give me help. I install MySQL-server-4.1.12-0.i386 package and MySQL--client--4.1.12-0.i386 package under root account. Under the root account, I can start the mysql and it seems working fine. However, when I change the root account to user account, it give me error report "Can't connect to local MySQL server through the socket "/var/lib/mysql/mysql.sock"" . But the mysql.sock file is already in this directory when I checked. I try a lot way to solve it, unfornately, it fails. Could some people give me some idea? I have to say I am just beginner. I am really appreciated any suggestions! Best Ying
