Re: can I just encrypt tables? what about the app?
On 29/02/2016 19:54, Gary Smith wrote: However, if TDE is employed, then you've got another significant obstacle to overcome: The data is only encrypted (aiui) once it's in memory. Apologies, that should read "unencrypted (aiui) once it's in memory" Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: can I just encrypt tables? what about the app?
On 29/02/2016 19:50, Reindl Harald wrote: cryptsetup/luks can achieve that way better Only to a degree. Once the disk is unencrypted, you've got access to the filesystem. If you've got physical access to the machine, then anything which gives you console access gives you (potentially) access to the underlying database files. If you can get those, it's trivial to get access to the dataset that they contain. However, if TDE is employed, then you've got another significant obstacle to overcome: The data is only encrypted (aiui) once it's in memory. At this point, you're needing to do attacks on RAM to get access to the data - and even then, you're unlikely to get 3 bars for a jackpot payout of the whole database schema, assuming a decent sized database. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: dump, drop database then merge/aggregate
On 29/02/2016 16:32, Steven Siebert wrote: At risk of giving you too much rope to hang yourself: if you use mysqldump to dump the database, if you use the --replace flag you'll convert all INSERT statements to REPLACE, which when you merge will update or insert the record, effectively "merging" the data. This may be one approach you want to look at, but may not be appropriate depending on your specific situation. I'd considered mentioning this myself, but this was the root of my comment about integrity - if the original database or tables are dropped, then the replace command will cause the data to poo all over the original dataset. As you mentioned in your (snipped) reply, this can go badly wrong in a short space of time without the correct controls in place. Even if they are in place, I'd have trouble sleeping at night if this were my circus. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: dump, drop database then merge/aggregate
On 29/02/2016 15:30, lejeczek wrote: On 28/02/16 20:50, lejeczek wrote: fellow users, hopefully you experts too, could help... ...me to understand how, and what should be the best practice to dump database, then drop it and merge the dumps.. What I'd like to do is something probably many have done and I wonder how it's done best. A box will be dumping a database (maybe? tables if it's better) then dropping (purging the data) it and on a different system that dump swill be inserted/aggregated into the same database. It reminds me a kind of incremental backup except for the fact that source data will be dropped/purged on regular basis, but before a drop, a dump which later will be used to sort of reconstruct that same database. How do you recommend to do it? I'm guessing trickiest bit might this reconstruction part, how to merge dumps safely, naturally while maintaining consistency & integrity? Actual syntax, as usually any code examples are, would be best. many thanks. I guess dropping a tables is not really what I should even consider - should I just be deleting everything from tables in order to remove data? And if I was to use dumps of such a database (where data was first cleansed then some data was collected) to merge data again would it work and merge that newly collected data with what's already in the database This sounds like a remarkably reliable way to ensure no data integrity. What exactly are you trying to achieve? Would replication be the magic word you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: create_time
update_time column works for MyISAM, not for InnoDB. On Mon, May 4, 2015 at 10:19 PM, Martin Mueller martinmuel...@northwestern.edu wrote: So, if you want to have a permanent record of when a table was created‹never mind subsequent adjustments, you should personally enter the data as a table comment? On 5/4/15, 9:13 AM, Johan De Meersman vegiv...@tuxera.be wrote: ...sigh. That sounds logical. I have, however, also had Martin's experience where create_time seemed improbable; and the structure is unlikely to have changed without my knowledge as user accounts don't have DML privileges. I didn't pay any further attention to it, though, as it wasn't important to me at the time. I'll be monitoring this thread with interest :-) - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: Pothanaboyina Trimurthy skd.trimur...@gmail.com Cc: Martin Mueller martinmuel...@northwestern.edu, MySql mysql@lists.mysql.com Sent: Monday, 4 May, 2015 16:11:24 Subject: Re: create_time That sounds logical. I have, however, also had Martin's experience where create_time seemed improbable; -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Rookie question
On 29/04/2013 18:29, Patrice Olivier-Wilson wrote: Hi all: I have a membership directory where folks can belong to more than one category. But all folks do not qualify for a category. So I want to list folks who have qualified in a category but not have them repeat. So if member 1 is in cat 3 and cat 5, I want their name only to show up once. Here's what I have so far, but it shows a member listed more than once. select distinct ? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: console input
Thanks, Shawn; I knew there was a better way to go about that. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
console input
Hi all, I've looked high and low for what I hope is a trivial answer. I was trying to load a table using LOAD DATA INFILE. Unfortunately, it craps out because there are some duplicate primary keys. Not surprising as the source did not enforce uniqueness. My problem is the load data simply dies without indicating which line of the input file was in error; the error message refers to line 3, which is not even the SQL statement for the LOAD DATA INTO statement: I can get the table loaded by specifying REPLACE INTO TABLE, but that still leaves me with not knowing where the duplicate records are. So... I wanted to read the data line at a time and use a plain INSERT statement. That way I could check for duplicate keys and discover where the duplicate records are. However, I can't find a way to read input from the console or a file. What am I missing? I know I could write a java or C++ program to do this, but it seems like overkill for what should be a trivial task. Thanks for any pointers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Postal code searching
On 24/04/2012 17:11, Tompkins Neil wrote: Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number of abbreviated postal codes like W1 WC1 WC2 NW1 Now, if I know the postal code W1J 7BX what is the best way using a MySQL query to get the abbreviated postal codes W1. Same if I have the postal code WC1H 8EJ, how do I get the abbreviated postal codes WC1 Can I use any matching patterns ? http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html Specifically, replace % with _ as this means match one character not match any number of characters. So, you can do: like W1 % like W1_ % etc. Does that help? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Postal code searching
On 24/04/2012 17:16, Gary Smith wrote: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html Specifically, replace % with _ as this means match one character not match any number of characters. So, you can do: like W1 % like W1_ % etc. Oh, and you can also get really dirty and start using string functions like left() and so on. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Postal code searching
On 24/04/2012 17:24, Tompkins Neil wrote: How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code Then you'd do: like 'W1 %' to return anything starting W1 like 'W13 %' to return anything starting W13. like 'W1U %' to return anything starting W1U. It's the location of the space before the wildcard that's important here. The space is taken as part of the known element of the string. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
On 13/02/2012 22:09, Haluk Karamete wrote: Gary, you've mentioned that the user would have had access to the sysobjects No, there's a difference. *If* they had access to sysobjects then it could've caused issues. Ideally, you should have some level of segregation within your database. That is, the user that is querying the database from the website doesn't have access (read/write) to sysobjects as this isn't required. To clarify - the credentials that are used on your website should only have the minimum level of access: If these details are for any reason compromised, then, should the person with those credentials get access to the back end, they'll only be able to see the information that they'd be able to on the website (albeit in a raw format). If they can see more than that, then the potential for damage increases significantly. Consider that you run your website with the root user credentials. These are compromised. The person with these now has your root user details and, as a result, makes a concerted effort to find a way in to your back end. Having successfully done this, they can then drop the database. Let's assume he did. The page that this attempt occurred is hard-wired to display a single record in detail view. In the code, I have a bunch of echo $row-title kind of statements... You could always escape these. Some SQL injection attacks do account for this kind of thing. They're typically much more sophisticated than the one you quoted. I'm even more curious now; what kind of goodies this evil user would have gotten with having access to the sysobjects from the query string? The family silver? Sysobjects contains pretty much everything about your database - information about tables, views, stored procedures. Consider it as something like a blueprint of your database. I mean how would my page display sysobjects data when I don't have anything to do with echo sysobjects stuff? It wouldn't be inconceivable to escape out of what you've done to protect yourself. I assume that if you're passing any input to the database that it's already escaped? can you shed some light maybe? Hopefully that helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: a sql injection attempt
On 13/02/2012 21:48, Haluk Karamete wrote: My logs shows that we have tried with a SQL Injection attempt, but our engine has detected and avoided it but I am just curious, what are these SQL statements are intending to achieve? SELECT * FROM lecturer WHERE recID='25 ' and exists (select * from sysobjects) and ''='' ORDER BY EntryDate DESC and SELECT * FROM lecturer WHERE recID='25' and char(124)+user+char(124)=0 and '%'='' ORDER BY EntryDate DESC If these were let in, what would have happened? Nothing on MySQL - however, if the back end was an MS SQL server then the first query would prove that the user had access to the sysobjects table (ie wasn't constrained within a view, etc). The second is - the char(124) evaluates to |user|=0. I'm not sure what this one does, tbh. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Out of sync tables
I have a innodb with a join on two tables. The foreign key is the id column which is set to AI. I have been having an issue of the tables being out of sync in that the id is not the same on the two tables. I have corrected this a couple of times in phpmyadmin by resetting the auto_increment to the same number in both tables, but it is re-occurring. I have tried to duplicate how this occurs, and I believe it is occurring on a page refresh, but I am not positive. I don't know what information / code to post that someone would be able to make a suggestion to a solution, so if someone could direct me to what to post I would be appreciative. -- Gary __ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
Michael, thank you for your reply
Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against
There is only one AI into the main page. This is the insert code, I have
probably left more in than you need to see.
What I also did was to add some duplicate columns in the two tables (email,
ip, timestamp) so in the event I need to manually to in I would be able to
decifer who goes where.
On second look, it would appear I am NOT using a join, but two inserts I
don't recall why I did it that way
**
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= ? . htmlentities($_SERVER['QUERY_STRING']);
}
$ipi_var_Recordset1 = -1;
if (isset($_SERVER['REMOTE_ADDR'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] :
addslashes($_SERVER['REMOTE_ADDR']);
}
if ((isset($_POST[MM_insert])) ($_POST[MM_insert] == form)) {
$insertSQL = sprintf(INSERT INTO attorney (email, firm_name, ip) VALUES
(%s, %s, %s),
GetSQLValueString($_POST['email'], text),
GetSQLValueString($_POST['firm_name'], text),
GetSQLValueString($_SERVER['REMOTE_ADDR'], text));
mysql_select_db($database_assess, $assess_remote);
$Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());
}
// checks if bot
if ($_POST['address'] != '' ){
die(Changed field);
}
$firmname_var_Recordset1 = -1;
if (isset($_POST['firm_name'])) {
$firmname_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['firm_name']
: addslashes($_POST['firm_name']);
}
$username_var_Recordset1 = -1;
if (isset($_post['user_name'])) {
$username_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['user_name']
: addslashes($_POST['user_name']);
}
$pw_var_Recordset1 = -1;
if (isset($_POST['password'])) {
$pw_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['password'] :
addslashes($_POST['password']);
}
$em_var_Recordset1 = -1;
if (isset($_POST['email_2'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['email_2'] :
addslashes($_POST['email_2']);
}
$ip_var_Recordset1 = -1;
if (isset($_SERVER['REMOTE_ADDR'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] :
addslashes($_SERVER['REMOTE_ADDR']);
}
mysql_select_db($database_assess, $assess_remote);
$query_Recordset1 = sprintf(SELECT law_firm.firm_id FROM law_firm WHERE
law_firm.firm_name = %s AND law_firm.user_name = %s AND law_firm.password=%s
AND law_firm.ip=%s , GetSQLValueString($firmname_var_Recordset1,
text),GetSQLValueString($username_var_Recordset1,
text),GetSQLValueString($pw_var_Recordset1,
text),GetSQLValueString($em_var_Recordset1,
text),GetSQLValueString($ip_var_Recordset1, text));
$Recordset1 = mysql_query($query_Recordset1, $assess_remote) or
die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$row = mysql_fetch_assoc($Recordset1);
$_SESSION['firm_id'] = $row['firm_id'];
echo $row['firm_id'];
if ((isset($_POST[MM_insert])) ($_POST[MM_insert] == form)) {
$insertSQL = sprintf(INSERT INTO law_firm (firm_name, user_name,
password, slsmn, email_2, ip) VALUES (%s, %s, %s, %s, %s, %s) ,
GetSQLValueString($_POST['firm_name'], text),
GetSQLValueString($_POST['user_name'], text),
GetSQLValueString($_POST['password'], text),
GetSQLValueString($_POST['slsmn'], text),
GetSQLValueString($_POST['email_2'], text),
GetSQLValueString($_SERVER['REMOTE_ADDR'], text));
mysql_select_db($database_assess, $assess_remote);
$Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());
Michael Dykman mdyk...@gmail.com wrote in message
news:BANLkTi=+fpW=-qbmb6tcfffhr1gb32w...@mail.gmail.com...
I presume you are inserting to both tables always at the same time
inside a transaction? We would need to see the code to see how you
are inserting them..
Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against
- michael dykman
On Fri, Apr 15, 2011 at 10:33 AM, Gary gp...@paulgdesigns.com wrote:
I have a innodb with a join on two tables. The foreign key is the id
column which is set to AI. I have been having an issue of the tables being
out of sync in that the id is not the same on the two tables. I have
corrected this a couple of times in phpmyadmin by resetting the
auto_increment to the same number in both tables, but it is re-occurring.
I have tried to duplicate how this occurs, and I believe it is occurring
on
a page refresh, but I am not positive.
I don't know what information / code to post
Re: Out of sync tables
Michael
I'm sorry, I should have removed this code for the post, but this code is
part of a honey pot. There is no 'address' input visible, it is used so
if a spam bot enters information into this field, it kills the form. Humans
cannot enter anything into this field.
if ($_POST['address'] != '' ) {
die(Changed field);
}
Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.
I'm not sure I undertand this, could you explain a little further for me.
Again, thank you for your help.
Gary
Michael Dykman mdyk...@gmail.com wrote in message
news:banlktimeyexp3lxozvgoz0fedrrf69r...@mail.gmail.com...
The first thing I notice browsing your code is this block stuck
immediately between your 2 insert statements:
if ($_POST['address'] != '' ) {
die(Changed field);
}
This guarantees that your 2 auto_increment sequences will fall out of
sync any time any client POSTs (and perhaps all gets?) to this script
without an 'address' parameter.
Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.
- michael dykman
On Fri, Apr 15, 2011 at 12:16 PM, Reindl Harald h.rei...@thelounge.net
wrote:
Am 15.04.2011 17:59, schrieb Gary:
Michael, thank you for your reply
Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against
There is only one AI into the main page. This is the insert code, I have
probably left more in than you need to see.
What I also did was to add some duplicate columns in the two tables
(email,
ip, timestamp) so in the event I need to manually to in I would be able
to
decifer who goes where.
On second look, it would appear I am NOT using a join, but two
inserts I
don't recall why I did it that way
this code is unreadable for me because of its coding-style and if i see
addslashes for database inserts i start to fear and run away
you are using two inserts so what do you do there and where can be
anything
out of sync on the database-level? where is the magic in your code without
using mysql_insert_id() or LAST_INSERT_ID() - what should this code do?
* insert in main table
* fetch mysql_insert_id() what is thread-safe
* use that value in the second table
and please do not use such ugly hacks as in the begin of your code
addslashes() has no useable security for user-input
even mysql_escape_string() has not - mysql_real_escape_string()
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
__ Information from ESET Smart Security, version of virus signature
database 6044 (20110415) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus signature
database 6044 (20110415) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
I'm sorry, I am unfamliar with an asc file, so I have not opened them. GAry Reindl Harald h.rei...@thelounge.net wrote in message news:4da87554.8030...@thelounge.net... __ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
Michael and Reindl Thank you both for you help and patience. I have inserted the $id = mysql_insert_id($connection) as well as the code in the INSERT clause and it seems to be working fine. One note is it turns out I did have AI on both tables, so that may have been adding a monkey wrench. I also took the advice to moved the honey pot code out of the middle of that code and moved it up. Again, thank you. Gary Reindl Harald h.rei...@thelounge.net wrote in message news:4da87902.9030...@thelounge.net... __ Information from ESET Smart Security, version of virus signature database 6045 (20110415) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Database Structure Opinions Please
I am at the planning stage of building a database that will have a fairly large amount of different information. It is a DB for law firms, so it will have all of their contact information including a county. Also an individual lawyer will be listed. Add to that 2 description areas (one for firm, one for lawyer), then there will be individual check boxes for areas of practice, I am thinking about 10. There will also be a need to store articles that will be written by these attorneys. I am thinking one main table with the firm info, a separate table for atty info, third for areas of practice and fourth for articles? I would enjoy hearing opinions on this. -- Gary __ Information from ESET Smart Security, version of virus signature database 5868 (20110212) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Records not being displayed
I have an issue that the first record in a query is not being displayed. It
seems that the first row in alphabetical order is not being brought to the
screen.
I have a MySQL DB that lists beers. I have a column for 'type' of beer
(imported, domestic, craft, light). The queries:
$result = MySQL_query(SELECT * FROM beer WHERE type = 'imported' AND stock
= 'YES' ORDER by beername );
When I run the query
if (mysql_num_rows($result) == !'0') {
$row = mysql_fetch_array($result);
echo 'h3Imported Beers/h3';
echo 'table width=100% border=0 cellspacing=1 cellpadding=1
id=tableone summary=
thBeer/th
thMaker/th
thType/th
thSingles/th
th6-Packs/th
thCans/th
thBottles/th
thDraft/th
thSize/th
thDescription/th';
while ($row = mysql_fetch_array($result)) {
echo 'tr td' . $row['beername'].'/td';
echo 'td' . $row['manu'] . '/td';
echo 'td' . $row['type'] . '/td';
echo 'td width=40' . $row['singles'] . '/td';
echo 'td width=20' . $row['six'] . '/td';
echo 'td width=40' . $row['can'] . '/td';
echo 'td width=20' . $row['bottles'] . '/td';
echo 'td width=40' . $row['tap'] . '/td';
echo 'td' . $row['size'] . '/td';
echo 'td' . $row['descrip'] . '/td';
'/tr';
}
echo '/tablebr /';
}
All but the first row in alphabetical order are displayed properly.
Can anyone tell me where I am going wrong?
--
Gary
__ Information from ESET Smart Security, version of virus signature
database 5715 (20101219) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Records not being displayed
Johnny
Thank you for your response, and you are correct. I have used that same
code for a number of other files and dont recall having that problem
(unfortunately, my recollection abilities seem to be withering).
So I removed the following lines and all works fineI just wish I could
recall why I had it in the first place.
if (mysql_num_rows($result) == !'0') {
$row = mysql_fetch_array($result);
Again, thank you for your help.
Gary
Johnny Withers joh...@pixelated.net wrote in message
news:aanlktik3nhjqkc00iw-nv1qqpdeaj1_5xtwbf7skd...@mail.gmail.com...
Your very first mysql_fetch_array consumes the first result.
On Dec 19, 2010 8:19 AM, Gary gp...@paulgdesigns.com wrote:
I have an issue that the first record in a query is not being displayed.
It
seems that the first row in alphabetical order is not being brought to the
screen.
I have a MySQL DB that lists beers. I have a column for 'type' of beer
(imported, domestic, craft, light). The queries:
$result = MySQL_query(SELECT * FROM beer WHERE type = 'imported' AND
stock
= 'YES' ORDER by beername );
When I run the query
if (mysql_num_rows($result) == !'0') {
$row = mysql_fetch_array($result);
echo 'h3Imported Beers/h3';
echo 'table width=100% border=0 cellspacing=1 cellpadding=1
id=tableone summary=
thBeer/th
thMaker/th
thType/th
thSingles/th
th6-Packs/th
thCans/th
thBottles/th
thDraft/th
thSize/th
thDescription/th';
while ($row = mysql_fetch_array($result)) {
echo 'tr td' . $row['beername'].'/td';
echo 'td' . $row['manu'] . '/td';
echo 'td' . $row['type'] . '/td';
echo 'td width=40' . $row['singles'] . '/td';
echo 'td width=20' . $row['six'] . '/td';
echo 'td width=40' . $row['can'] . '/td';
echo 'td width=20' . $row['bottles'] . '/td';
echo 'td width=40' . $row['tap'] . '/td';
echo 'td' . $row['size'] . '/td';
echo 'td' . $row['descrip'] . '/td';
'/tr';
}
echo '/tablebr /';
}
All but the first row in alphabetical order are displayed properly.
Can anyone tell me where I am going wrong?
--
Gary
__ Information from ESET Smart Security, version of virus
signature
database 5715 (20101219) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
__ Information from ESET Smart Security, version of virus
signature database 5715 (20101219) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus signature
database 5715 (20101219) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error in mysql replication with LOAD DATA INFILE
Hi Anand, Just try 'load data local infile',it maybe work. Eric 2010/12/20 Anand anand@gmail.com: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error '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 ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: 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 ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Password Reset
I recieved a call from a client saying the web site did not work, turns out the database password was reset, and not by me. In looking in the DB after the PW was reset, I could find nothing out of place, although frankly I was not sure what to look for. Is this indicitive of an attack? Is this something to worry about? I had (or so I assumed) plenty of protections on the files, including one of the more popular anit-spam/injection attack systems. Any guidance on this would be appriciated. -- Gary __ Information from ESET Smart Security, version of virus signature database 5708 (20101216) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
GRANT ALL error - newbee
I'm attempting to set up a Linux Apache Mysql PHP (LAMP) system for the first time. On my internal network (behind firewall) I have a computer (cruncher) that is acting as the web server. Another computer (supercrunch) is being used as the home for Dupal6. I connected to the cruncher system from supercrunch with mysql -u root -h cruncher -p. This seemed to work fine. But, one of the setup statements follows along with the result. I can't find the error. Help! mysql GRANT ALL PRIVILAGES ON *.* TO 'g...@supercrunch' IDENTIFIED BY 'password' WITH GRANT OPTION; ERROR 1064 (42000): 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 'PRIVILAGES ON *.* TO 'g...@supercrunch' IDENTIFIED BY 'qatip' WITH GRANT OPTION' at line 1 Leaving out the quotes makes no difference. Gary R -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join syntax problem
I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join syntax problem
Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Getting Array to display on SELECT
I'm frankly not sure if this is a MySQL question or PHP, but I thought I
would start here.
I have a form that I have a (ever growing) list of checkboxes, Here is a
sample of the code for it.
input name=keyword[] type=checkbox value=fox /
It seems to go in, when I say seems to, I get a result of Array in the
table, the code is listed below. I have tried various solutions I found in
searching the issue, but have only been able to so far get Array.
echo 'table border=1thId Number/ththDate
Entered/ththCaption/ththWhere
Taken/ththKeywords/ththDescription/ththImage/th';
while ($row = mysqli_fetch_array($data)) {
echo 'trtd' . $row['image_id']. '/td';
echo 'td' . $row['submitted']. '/td';
echo 'td' . $row['caption']. '/td';
echo 'td' . $row['where_taken'] . '/td';
echo 'td' . $row['keyword']. '/td';
echo 'td' . $row['description'] . '/td';
if (is_file($row['image_file'])) {
echo 'tdimg src='.$row['image_file'].' width=100px
height=100px//td';
}
As a bonus question, does anyone have any idea why the image would show up
in IE9, and not FF?
Thanks for your help.
Gary
__ Information from ESET NOD32 Antivirus, version of virus signature
database 5045 (20100420) __
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT INTO multiple tables
Colin
Thank you for your reply. I had previously found the page you linked in
your original post, however at this stage of my development, my imagination
creates error messages and not inovative solutions.
I have found some other issues that are presenting road blocks, once I solve
them, I will try to apply your solutions to the original problem.
Thanks again for your reply.
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004122103.15609.co...@obviouslymalicious.com...
Normally I would avoid getting into this sort of argument ( The 'OMG
someone on teh internets are
wrong!!' argument)
But in this case, the solution ( still the first result in a google
search) is far more efficient than
closing a connection so you can insert into another table.
You are correct in that the example given doesn't do exactly what you are
trying to do, but a little
imagination stretches it into exactly what you want.
Exhibit A:
Here I have created 2 test tables in 'testdb' and inserted an A into the
first.
mysql create database testdb
- ;
Query OK, 1 row affected (0.08 sec)
mysql connect testdb;
Connection id:72
Current database: testdb
mysql create table test1( t1 VARCHAR(1) );
Query OK, 0 rows affected (0.13 sec)
mysql create table test2( t2 VARCHAR(1) );
Query OK, 0 rows affected (0.00 sec)
mysql insert into test1 VALUES( 'A' ); insert into test2 VALUES('B');
Query OK, 1 row affected (0.08 sec)
Query OK, 1 row affected (0.00 sec)
mysql select * from test1; select * from test2;
+--+
| t1 |
+--+
| A|
+--+
1 row in set (0.02 sec)
+--+
| t2 |
+--+
| B|
+--+
1 row in set (0.00 sec)
###
As you can see, a single statement is being used to insert into multiple
rows, a clue this can be
done with a script as well... but I digress... we will get to that.
Exhibit B
Here I have copied the final script from the page that I said contained
the answer.
Source is: http://www.hiteshagrawal.com/mysql/mysql-batch-insert-using-php
I've changed 2 things:
1. The connection info ( except the password obviously ) corresponds to my
test setup
2. The query was changed to make sense for my test setup
?php
$batchconnection = new mysqli('localhost', 'root', 'hunter2', 'testdb');
if ($batchconnection-connect_error) {
echo Error Occurred While Connection To DataBase;
}
$sqlStatements = insert into test1(t1) values('1');insert into
test2(t2) values('2');;
$sqlResult = $batchconnection-multi_query($sqlStatements);
if($sqlResult == true) {
echo Successfully Inserted Records;
} else {
echo Some Error Occured While Inserting Records;
}
?
So then we execute it:
co...@somethingelse:~/phpcrap$ ./tester.php
Successfully Inserted recordsco...@somethingelse:~/phpcrap$
That looks promising.
Exhibit C
Finally, lets see what our database looks like
mysql select * from test1; select * from test2;
+--+
| t1 |
+--+
| A|
| 1|
+--+
2 rows in set (0.00 sec)
+--+
| t2 |
+--+
| B|
| 2|
+--+
2 rows in set (0.00 sec)
Oh wow... it worked!
Since it is your script, the way you ultimately do it is your option...
I would suggest that you do not disconnect between inserts, this is
terribly inefficient.
Colin
On April 12, 2010 05:57:42 pm Gary wrote:
Michael
Thank you for your response. It gave me the idea how to solve this, and
it
seemed to have worked!
For those following hoping to see a solution, what I did was open the
connection, insert into one table, closed the connection, closed the php
script, and the data was inserted into 2 of the tables... The code looks
like this:
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
to MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
to MySQL server');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
It seems a bit redundant for php, but it seems to work.
If by the way anyone sees a problem with this solution, I would love to
read it.
Again, thank you for your response.
Gary
Michael Dykman mdyk...@gmail.com wrote in message
news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fd...@mail.gmail.com...
It is not a question of multiple tables, it is a question of multiple
statements. Most PHP configurations prohibit the application of more
than one statement per call to execute. This is generally thought to
be a security issue as the vast majority of simple PHP-based SQL
injection attacks only work on servers that allow multiple statements.
I
Re: INSERT INTO multiple tables
Chris.
Thanks for your response. I read somewhere that the mysqli was better, so I
have been using it.
This exercise is an experiment, on my local machine only, so I ommitted any
escape functions. I am trying to create DB's with multiple tables, so it is
totally a learning exercise.
So hopefully I will learn from my mistakes.
Thanks for your help.
Gary
Chris W 4rfv...@cox.net wrote in message
news:4bc47b0b.7020...@cox.net...
I have no idea how you got here but there is no reason to do it that way.
This will work just fine and I do it every day in php.
However I don't use mysqli I still use ...
mysql_connect
mysql_select_db
mysql_real_escape_string
mysql_query
Don't forget to use the mysql_real_escape_string function to be sure sql
injection can't happen.
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
to
MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
Gary wrote:
Michael
Thank you for your response. It gave me the idea how to solve this, and
it seemed to have worked!
For those following hoping to see a solution, what I did was open the
connection, insert into one table, closed the connection, closed the php
script, and the data was inserted into 2 of the tables... The code looks
like this:
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
to MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
to MySQL server');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
It seems a bit redundant for php, but it seems to work.
If by the way anyone sees a problem with this solution, I would love to
read it.
Again, thank you for your response.
Gary
Michael Dykman mdyk...@gmail.com wrote in message
news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fd...@mail.gmail.com...
It is not a question of multiple tables, it is a question of multiple
statements. Most PHP configurations prohibit the application of more
than one statement per call to execute. This is generally thought to
be a security issue as the vast majority of simple PHP-based SQL
injection attacks only work on servers that allow multiple statements.
I haven't been deep in PHP land for a little while, but I think you
will find the default driver/config is expressly preventing you from
doing this.
- michael dykman
On Mon, Apr 12, 2010 at 9:44 AM, Gary gwp...@ptd.net wrote:
Seriously
You should read your answers before you post, the SA link did not
provide
the answer. Had you read the page you sent, you would notice it does not
apply to mulitple tables...
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004112310.16594.co...@obviouslymalicious.com...
Seriously...
I found the answer in the first result.
http://lmgtfy.com/?q=mysqli+multiple+insert+statements
Assuming mysqli, if you are using a different driver, then google that
Colin
On April 11, 2010 10:36:41 pm viraj wrote:
is it mysqli query or 'multi_query'?
http://php.net/manual/en/mysqli.multi-query.php
~viraj
On Sun, Apr 11, 2010 at 10:27 PM, Gary gwp...@ptd.net wrote:
I am experimenting with multiple tables, it is only a test that is my
local machine only. This is the current code, which does not work , I
have tried to concatonate the insert statements. I have tried
multiple
$query variables, but it is just overwriting itself (only the last
one
gets inserted). I also tried writing the $query as an array, which
got
me
an error message (saying it was expecting a string and I offered an
array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br
/br
/
labelLast Name /labelinput name=lname type=text /br /br
/
labelStreet Address /labelinput name=street type=text /br
/br /
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone
Re: INSERT INTO multiple tables
Seriously
You should read your answers before you post, the SA link did not provide
the answer. Had you read the page you sent, you would notice it does not
apply to mulitple tables...
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004112310.16594.co...@obviouslymalicious.com...
Seriously...
I found the answer in the first result.
http://lmgtfy.com/?q=mysqli+multiple+insert+statements
Assuming mysqli, if you are using a different driver, then google that
Colin
On April 11, 2010 10:36:41 pm viraj wrote:
is it mysqli query or 'multi_query'?
http://php.net/manual/en/mysqli.multi-query.php
~viraj
On Sun, Apr 11, 2010 at 10:27 PM, Gary gwp...@ptd.net wrote:
I am experimenting with multiple tables, it is only a test that is my
local machine only. This is the current code, which does not work , I
have tried to concatonate the insert statements. I have tried multiple
$query variables, but it is just overwriting itself (only the last one
gets inserted). I also tried writing the $query as an array, which got
me
an error message (saying it was expecting a string and I offered an
array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br /br
/
labelLast Name /labelinput name=lname type=text /br /br /
labelStreet Address /labelinput name=street type=text /br
/br /
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone/labelinput name=phone type=text /br /br /
labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
$comments=($_POST['comments']);
$REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
$dbc=mysqli_connect('localhost','root','','test');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip').
INSERT INTO comments(comments).VALUES('$comments').
INSERT INTO
contact(phone,fax,email).VALUES('$phone','$fax','$email'). INSERT
INTO name (fname, lname).VALUES('$fname','$lname');
$result = mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
/body
/html
__ Information from ESET Smart Security, version of virus
signature database 5017 (20100411) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=kali...@gmail.com
--
It is easy to find fault, if one has that disposition. There was once a
man
who, not being able to find any other fault with his coal, complained that
there were too many prehistoric toads in it.
-- Mark Twain, Pudd'nhead Wilson's Calendar
__ Information from ESET Smart Security, version of virus
signature database 5021 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus signature
database 5021 (20100412) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT INTO multiple tables
Michael
Thank you for your response. It gave me the idea how to solve this, and it
seemed to have worked!
For those following hoping to see a solution, what I did was open the
connection, insert into one table, closed the connection, closed the php
script, and the data was inserted into 2 of the tables... The code looks
like this:
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
It seems a bit redundant for php, but it seems to work.
If by the way anyone sees a problem with this solution, I would love to read
it.
Again, thank you for your response.
Gary
Michael Dykman mdyk...@gmail.com wrote in message
news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fd...@mail.gmail.com...
It is not a question of multiple tables, it is a question of multiple
statements. Most PHP configurations prohibit the application of more
than one statement per call to execute. This is generally thought to
be a security issue as the vast majority of simple PHP-based SQL
injection attacks only work on servers that allow multiple statements.
I haven't been deep in PHP land for a little while, but I think you
will find the default driver/config is expressly preventing you from
doing this.
- michael dykman
On Mon, Apr 12, 2010 at 9:44 AM, Gary gwp...@ptd.net wrote:
Seriously
You should read your answers before you post, the SA link did not provide
the answer. Had you read the page you sent, you would notice it does not
apply to mulitple tables...
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004112310.16594.co...@obviouslymalicious.com...
Seriously...
I found the answer in the first result.
http://lmgtfy.com/?q=mysqli+multiple+insert+statements
Assuming mysqli, if you are using a different driver, then google that
Colin
On April 11, 2010 10:36:41 pm viraj wrote:
is it mysqli query or 'multi_query'?
http://php.net/manual/en/mysqli.multi-query.php
~viraj
On Sun, Apr 11, 2010 at 10:27 PM, Gary gwp...@ptd.net wrote:
I am experimenting with multiple tables, it is only a test that is my
local machine only. This is the current code, which does not work , I
have tried to concatonate the insert statements. I have tried multiple
$query variables, but it is just overwriting itself (only the last one
gets inserted). I also tried writing the $query as an array, which got
me
an error message (saying it was expecting a string and I offered an
array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br /br
/
labelLast Name /labelinput name=lname type=text /br /br
/
labelStreet Address /labelinput name=street type=text /br
/br /
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone/labelinput name=phone type=text /br /br /
labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
$comments=($_POST['comments']);
$REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
$dbc=mysqli_connect('localhost','root','','test');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip').
INSERT INTO comments(comments).VALUES('$comments').
INSERT INTO
contact(phone,fax,email).VALUES('$phone','$fax','$email'). INSERT
INTO name (fname, lname).VALUES('$fname','$lname');
$result = mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
/body
/html
__ Information from ESET Smart Security, version of virus
signature database 5017 (20100411) __
The message was checked by ESET Smart Security.
http
INSERT INTO multiple tables
I am experimenting with multiple tables, it is only a test that is my local
machine only. This is the current code, which does not work , I have tried
to concatonate the insert statements. I have tried multiple $query
variables, but it is just overwriting itself (only the last one gets
inserted). I also tried writing the $query as an array, which got me an
error message (saying it was expecting a string and I offered an array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br /br /
labelLast Name /labelinput name=lname type=text /br /br /
labelStreet Address /labelinput name=street type=text /br /br
/
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone/labelinput name=phone type=text /br /br /
labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
$comments=($_POST['comments']);
$REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
$dbc=mysqli_connect('localhost','root','','test');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip').
INSERT INTO comments(comments).VALUES('$comments').
INSERT INTO contact(phone,fax,email).VALUES('$phone','$fax','$email').
INSERT INTO name (fname, lname).VALUES('$fname','$lname');
$result = mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
/body
/html
__ Information from ESET Smart Security, version of virus signature
database 5017 (20100411) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Tokutek Acquires Oracle
Krishna Chandra Prajapati wrote: Hi guys, Is the information is true. http://planet.mysql.com/ http://tokutek.com/2010/04/tokutek-acquires-oracle/ Might want to check the date. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: table export in cron
machiel.richards wrote: How can we do this when running in a cron script? mysql -e select * from table into outfile '/path/to/output/file' fields terminated by '|' ? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Render row without duplicates
bharani kumar wrote: Hi My fields something like hospital1,hospital2,hospital3,patientname, [...] i know , i can display all hospital code with unique , but i dont in the single column , with unique record, Can you tell me how to do this ? Would it be possible to reconsider your table design? Instead of having the above, have something such as: Person(ID,Name) Hospital(ID,Name,Code) LinkTable(ID,PersonID,HospitalID) You'd then have something such as: Person(1,John) Hospital(800,Bart's London,1234) LinkTable(1000,1,800) You'd then be able to find all of the distinct hospital codes by doing select distinct code from hospital. Apologies if this isn't possible. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Render row without duplicates
Benedikt Schackenberg wrote: Am 02.01.2010 13:43, schrieb bharani kumar: No Duplicate records, select hospital1code from *yourtable* grop by hospital1 This won't work as he's also looking for entities in the hospital2code and hospital3code fields to be returned in the same resultset, but as a single column. Essentially, hospital1code, hospital2code and hospital3code need to be merged to a single column, deduped, and then returned. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Patrice Olivier-Wilson wrote: I have 2 databases, different domains. Both have a table named 'tips'... both have different contents in the table. Using phpMyAdmin for GUI. I want to export databaseA tips as sql (done) then import content into databaseB tips. But when I run that operation, the databaseB says that there is already a table named tips in databaseB. Yep, know that... I want to bring in the contents...not make a new table. Any help, most appreciated When you export, PHPMyAdmin has the option to add drop table. This will drop the existing table structure and create a new one as it was when it was exported. Is this what you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Patrice Olivier-Wilson wrote: I have data I need to keep in both db just trying to merge. There's two ways around this: First is to not export the structure (uncheck structure). The second is to export with if not exists. This should (IIRC) do a create table if not exists, so it'll do what you're wanting to do. Do you have any primary keys/auto increment columns that are going to overlap or anything like that? Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing table contents
Patrice Olivier-Wilson wrote: Gave it a try got this: MySQL said: #1062 - Duplicate entry '1' for key 1 Yeah, that's what I was saying about in my previous mail. It looks like you've got a primary key on one of your columns, and you're attempting to insert data into it with a duplicate primary key (ie what the error message says). The easiest way to get around this one would be to write a query that pulls all of the columns apart from the primary key, and then replace that field with '' or somesuch. For instance, let's say you've got a schema of the following: table1(primarykey,column2,column3,column4,column5) primarykey is obviously a primary key. You'd do something along the lines of select '',column2,column3,column4,column5 from table1; Then export that resultset to an SQL file. Anyone else aware of an easier way to do this? I've got into some bad habits over the years, but I'm not aware of another way to do what Patrice is trying to do. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database fundamentals: wanna learn.
Ken D'Ambrosio wrote: Hey, all. I've been using databases clear back to xBase days; that being said, I've never had a solid foundation for relational databases. While I can muddle by in SQL, I really don't have a good understanding of exactly how keys are set up, the underpinnings of indexing, and, oh, lots of ground-level stuff. Call me a user, and you'd be right -- an administrator of databases? Not so much. So, any suggestions -- books, courses, web sites, what-have-you -- that I should be hitting up so I can have a better grasp of what's going on behind the scenes? I recently attended Sun's MySQL DBA course (http://www.mysql.com/training/schedule.php?class=5200) which I can heartily recommend. The course covers the kind of things you're after, including indexing, how the engines work (ie pros and cons), backups, etc. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Victor Subervi wrote: On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? Would max() work for you? This isn't necessarily foolproof, as it would show the highest ID if you used max(id), for instance - this won't necessarily be what you were expecting, but in most cases will be what you'd imagine it would be. An example of where it wouldn't be: Although ID is auto_increment, you could define a row as, say, '10005583429'. This would be a valid input. Selecting max(id) would return that number. However, auto_increment wouldn't change - it would still be '34' (or whatever) for the next line. Thus, max(id) would be wrong for however long it takes for auto_increment to get to that figure, which could potentially be a long time. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch which changes this behaviour, or is my mind dribbling out of my ears? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: all tables with certain type
walter harms wrote: hi list, is it possible to get a list of all tables with a certain type in one statement ? for now i collect all tables (show tables) and search for the type (show columns). Any way to circumvent that ? make it one statement ? use information_schema; select column_name,data_type from columns where data_type='YOUR DATA TYPE HERE'; Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple joins from same table?
Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. select * from table as t1,table as t2,table as t3 where t1.column1='blah' and t2.column2='blah' and t3.column3='blah' does the same thing as select * from table where column1='blah' and column2='blah' and column3='blah' Does that do what you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL being hacked with commands through URL
James Coffman wrote: Hello all, My website has been hacked using a url such as: -1%20union%20all%20select%201,2,concat(username,char(58),password),4,5,6%20f rom%20users-- . I have been searching on the web for a solution/fix to this issue and I cannot seem to find one. The command above is showing all usernames and passwords (in hashes) and I am not comfortable with that at all! Is there anyone out there that may be able to help or may be able to point me in the direction that I need to go in order to correct this issue? The term you're looking for is SQL injection. Pop that into Google and you'll get a shedload of stuff. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slave log files going nuts...
-Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, July 17, 2009 2:02 PM To: Gary Smith; Todd Lyons Cc: mysql@lists.mysql.com Subject: RE: Slave log files going nuts... The binlogs are closed and reopened every time you do a FLUSH LOGS; command, or when the server restarts. Is your server crashing continuously? Take a look at your error log as well. Regards, Gavin Towey Gavin/Todd, Tracking it down, the timestamp correlates with a mysqldump that I have a crontab for which of course, has flush-logs. /usr/bin/mysqldump --opt --flush-logs --all-databases --result-file=$FILENAME I'm not sure if we want to flush the log files on this machine at this point. It's a tossup since there are multiple slaves hitting this guy replicating only specific tables in some cases. I would guess that we could always restore the entire backup and dump what we want across to a new server before starting the incremental replication, but I'm still working that plan up right now. Anyway, thanks for the pointers to indentify the problem. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slave log files going nuts...
I have a new slave I setup against a new master server. The master server has 4 log files in it, the most current being updated on the 16th. The slave server on the other hand has several files, many which seem to be blank. This slave is set to slave the master and act as a master for downstream slaves. Note, there is no master/master on this configuration, even though the master itself could do it. Below is the my.cnf file and a listing of the files generated on the slave. This portion of the file is unique on both machines. Only differences in the file are the server-id and report-host. Both servers are running 5.1.35 Any ideas? #INNO DB settings innodb_file_per_table innodb_flush_log_at_trx_commit = 1 innodb_autoextend_increment = 2M innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 48M innodb_log_file_size= 10M innodb_log_buffer_size = 64M innodb_log_files_in_group = 1 log-slave-updates sync_binlog = 1 replicate-same-server-id= 0 log_bin_trust_function_creators = 1 log-bin = /exports/mysql-log/log-repl binlog-ignore-db= mysql binlog-ignore-db= information_schema replicate-ignore-db = mysql replicate-ignore-db = information_schema relay-log = hs-relay-bin relay-log-index = hs-relay-index relay-log-info-file = hs-relay-info relay-log-purge = 1 -rw-rw 1 mysql mysql 125 Jul 11 23:43 log-repl.01 -rw-rw 1 mysql mysql 125 Jul 11 23:43 log-repl.02 -rw-rw 1 mysql mysql 125 Jul 11 23:43 log-repl.03 -rw-rw 1 mysql mysql 2350918 Jul 14 08:33 log-repl.04 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.05 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.06 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.07 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.08 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.09 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.10 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.11 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.12 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.13 -rw-rw 1 mysql mysql 148 Jul 14 08:33 log-repl.14 -rw-rw 1 mysql mysql 117655 Jul 14 11:24 log-repl.15 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.16 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.17 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.18 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.19 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.20 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.21 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.22 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.23 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.24 -rw-rw 1 mysql mysql 148 Jul 14 11:24 log-repl.25 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.26 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.27 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.28 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.29 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.30 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.31 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.32 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.33 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.34 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.35 -rw-rw 1 mysql mysql 148 Jul 14 11:25 log-repl.36 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.37 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.38 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.39 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.40 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.41 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.42 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.43 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.44 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.45 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.46 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.47 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.48 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.49 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.50 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.51 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.52 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.53 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.54 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.55 -rw-rw 1 mysql mysql 148 Jul 14 11:26 log-repl.56
RE: Slave log files going nuts...
I'll have to look into that. We have a single cronjob that just does a mysqldump daily but not during the time of the log file generation, but that's all I can think of off the top of my head. Gary From: tly...@sitehelp.org [tly...@sitehelp.org] On Behalf Of Todd Lyons [tly...@ivenue.com] Sent: Thursday, July 16, 2009 2:41 PM To: Gary Smith Cc: mysql@lists.mysql.com Subject: Re: Slave log files going nuts... On Thu, Jul 16, 2009 at 1:18 PM, Gary Smithg...@primeexalia.com wrote: I have a new slave I setup against a new master server. The master server has 4 log files in it, the most current being updated on the 16th. The slave server on the other hand has several files, many which seem to be blank. This slave is set to slave the master and act as a master for downstream slaves. Note, there is no master/master on this configuration, even though the master itself could do it. Any ideas? Something is doing several 'mysqladmin refresh' or a related command, all sequentially in a row in short order. Look at your cron jobs that start or end around the time that all those empty binlogs are being created. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: XAMMP-MySQL crashing
I'm sorry for the delay in thanking those that offered help, I have not been able to look at this problem for a day or so, but thank you. And Thank you Martin for your note. I have not backed up anything on this, however it was really just a practice run, so I will not really lose anything, but a very good lesson. I did find a file that had all of the databases in it, I wonder if I copy all of those if I would be able to save whatever DB's I had. Thanks again and if anyone thinks of anything new, I would love to hear it. Gary Gary gwp...@ptd.net wrote in message news:20090711165227.4655.qm...@lists.mysql.com... I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary __ Information from ESET Smart Security, version of virus signature database 4241 (20090714) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET Smart Security, version of virus signature database 4241 (20090714) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Copy 70GB ibdata, etc. and server won't start now
Daevid, --The logfiles are the same size so I assume that's configured right? They should be. If you copied the file over, mysql isn't going to increase/decrease the size of the file (because it already exists). I suspect that you didn't save the old my.cnf file. The only other question I have is why is the New file smaller than the original. innodb doesn't shrink files to the best of my knowledge (or at least, it hasn't for me) -- or did you mean that the top one is the new, and the bottom one is the old.. So, looking a little harder at your files, I bet you tried to start myself against the USB device directly, on the first try, it failed, so you copied the data over to the local disk and you have been trying to recover it since. I say that because the time stamp on the usb (new or old ones) but have July 14th as the dates... I think as others have mentioned, the startup recovery flags might be your best bet. From: Daevid Vincent [dae...@daevid.com] Sent: Tuesday, July 14, 2009 12:23 PM To: mysql@lists.mysql.com Cc: Gary Smith Subject: RE: Copy 70GB ibdata, etc. and server won't start now -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Monday, July 13, 2009 8:54 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: Copy 70GB ibdata, etc. and server won't start now InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: for more information. InnoDB: Error: trying to access page number 2144600306 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 090714 1:43:18InnoDB: Assertion failure in thread 3083368144 in file fil0fil.c line 3959 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. First thing that comes to mind is a scenario that happened some time ago when we migrated data from one server to another in a similar way. Server one had the innodb file set to 2gb each file (10 files total). New server was set for 1gb each. It doesn't shrink files so not much was thought about it at the time but our problem was the innodb table settings had to match to the letter. We ended up copying the copy file from the old machine to the new machine (they were running the same version so it really wasn't a problem. The logfiles are the same size so I assume that's configured right? I know that you stated you were running Ubuntu, which is great, but what version of the database did it come from and what version of the database is it going to? +--+ | Old: | +--+ mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 drwx-- 2 mysql mysql 12288 2009-06-26 21:33 agis_core -rw-r--r-- 1 mysql mysql 0 2008-11-24 23:34 debian-5.0.flag -rw-rw 1 mysql mysql 72387395584 2009-07-14 19:18 ibdata1 -rw-rw 1 mysql mysql 5242880 2009-07-14 19:18 ib_logfile0 -rw-rw 1 mysql mysql 5242880 2009-07-14 18:30 ib_logfile1 drwxr-xr-x 2 mysql mysql4096 2008-11-24 23:34 mysql +--+ | New: | +--+ mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2 drwx-- 2 mysql mysql 12288 2009-07-14 00:36 agis_core -rw-r--r-- 1 mysql mysql 0 2008-11-24 23:34 debian-5.0.flag -rw-rw 1 mysql mysql 70038585344 2009-06-17 04:09 ibdata1 -rw-rw 1 mysql mysql 5242880 2009-07-14 01:43 ib_logfile0 -rw-rw 1 mysql mysql 5242880 2009-06-17 03:22 ib_logfile1 drwxr-xr-x 2 mysql mysql4096 2009-07-14 00:36 mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Copy 70GB ibdata, etc. and server won't start now
Johnny, I'm less worried about the month in between than the fact that all of the dates for the files on his USB data should be roughly the same. It looks to me like he tried to start it against the data on the USB drive. Another question is, was this an cold backup or hot backup? If this were a hot backup, I could see this problem happening. If it were a could backup, it should work. From: Johnny Withers [joh...@pixelated.net] Sent: Tuesday, July 14, 2009 1:40 PM To: Daevid Vincent Cc: mysql@lists.mysql.com; Gary Smith Subject: Re: Copy 70GB ibdata, etc. and server won't start now Why do the dates on the log files differ by almost a month between Old and New? On Tue, Jul 14, 2009 at 2:23 PM, Daevid Vincent dae...@daevid.commailto:dae...@daevid.com wrote: -Original Message- From: Gary Smith [mailto:g...@primeexalia.commailto:g...@primeexalia.com] Sent: Monday, July 13, 2009 8:54 PM To: Daevid Vincent; mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: RE: Copy 70GB ibdata, etc. and server won't start now InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: for more information. InnoDB: Error: trying to access page number 2144600306 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 090714 1:43:18InnoDB: Assertion failure in thread 3083368144 in file fil0fil.c line 3959 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.comhttp://bugs.mysql.com/. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. First thing that comes to mind is a scenario that happened some time ago when we migrated data from one server to another in a similar way. Server one had the innodb file set to 2gb each file (10 files total). New server was set for 1gb each. It doesn't shrink files so not much was thought about it at the time but our problem was the innodb table settings had to match to the letter. We ended up copying the copy file from the old machine to the new machine (they were running the same version so it really wasn't a problem. The logfiles are the same size so I assume that's configured right? I know that you stated you were running Ubuntu, which is great, but what version of the database did it come from and what version of the database is it going to? +--+ | Old: | +--+ mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 drwx-- 2 mysql mysql 12288 2009-06-26 21:33 agis_core -rw-r--r-- 1 mysql mysql 0 2008-11-24 23:34 debian-5.0.flag -rw-rw 1 mysql mysql 72387395584 2009-07-14 19:18 ibdata1 -rw-rw 1 mysql mysql 5242880 2009-07-14 19:18 ib_logfile0 -rw-rw 1 mysql mysql 5242880 2009-07-14 18:30 ib_logfile1 drwxr-xr-x 2 mysql mysql4096 2008-11-24 23:34 mysql +--+ | New: | +--+ mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2 drwx-- 2 mysql mysql 12288 2009-07-14 00:36 agis_core -rw-r--r-- 1 mysql mysql 0 2008-11-24 23:34 debian-5.0.flag -rw-rw 1 mysql mysql 70038585344 2009-06-17 04:09 ibdata1 -rw-rw 1 mysql mysql 5242880 2009-07-14 01:43 ib_logfile0 -rw-rw 1 mysql mysql 5242880 2009-06-17 03:22 ib_logfile1 drwxr-xr-x 2 mysql mysql4096 2009-07-14 00:36 mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.netmailto:joh...@pixelated.net
RE: Copy 70GB ibdata, etc. and server won't start now
InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: for more information. InnoDB: Error: trying to access page number 2144600306 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 090714 1:43:18InnoDB: Assertion failure in thread 3083368144 in file fil0fil.c line 3959 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. First thing that comes to mind is a scenario that happened some time ago when we migrated data from one server to another in a similar way. Server one had the innodb file set to 2gb each file (10 files total). New server was set for 1gb each. It doesn't shrink files so not much was thought about it at the time but our problem was the innodb table settings had to match to the letter. We ended up copying the copy file from the old machine to the new machine (they were running the same version so it really wasn't a problem. I know that you stated you were running Ubuntu, which is great, but what version of the database did it come from and what version of the database is it going to? Anyway, if the original server is still up, I'd just copy from one store to the other. It might be slow to do a 4 day export, but if you are two days into this the savings of USB copy has already been lost. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication, Stored Proceedures and Databases
-Original Message- From: sjm...@pobox.com [mailto:sjm...@pobox.com] Sent: Saturday, July 11, 2009 1:02 AM To: mysql@lists.mysql.com Subject: Re: Replication, Stored Proceedures and Databases g...@primeexalia.com (Gary Smith) writes: ... In database G we have 150+ stored procedures. 150k stored procedures? Sounds rather large. Do you really need this? 150, not 150,000. What's the best approach to fix this problem? Is it as simple as adding the appropriate USE statement inside of the stored procedure right before the insert/update/delete/whatever? I'd suggest row based replication. In your previous post you mentioned you were using 5.1.35 so you can do that. One of the reasons for using RBR is precisely to make life clearer when replicating from one server to another. The rows changed on the master will be changed on the slave. You don't need to depend on the effect of the stored procedure on master and slave being the same. We are using row based replication. In the wee hours of the night last night we changed the replication rules to replicate everything but information_schema and mysql. No changes that are initiated from the stored procedures in database G that update tables in database A are seen. We had to do an entire DB reload last night after we discovered this. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Re: Replication, Stored Proceedures and Databases
-Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Saturday, July 11, 2009 2:44 AM To: Simon J Mudd Cc: mysql@lists.mysql.com Subject: R: Re: Replication, Stored Proceedures and Databases You dont have changes coming from db G since it is ignored from replication. Why dont You move all stored procs in a separate db and replicate it as well? You will use it as a 'library' for all of your dbs. Of course prepose your schema name, always. You dont have to change replication type in This case. Cheers, Claudio Database G is just that, a library for the stored procedures. Anyway, as mentioned in the other email, replicating all of the tables solved the problem. As for the schema name, I always include it on all queries. I found that it was much easier to always to it instead of only doing it when I need to and forgetting. I work with Oracle and I'm always doing stuff in another schema so I do it out of habit. Thanks guys for the follow up. I think someone should add a clear statement to the doc's regarding the cross schema replication for stored procedures and tables, when the active database is not the replicated database, even though the affected table is in a replicated database. The docs are currently vague on this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
XAMMP-MySQL crashing
I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: XAMMP-MySQL crashing
Not sure if this is it, it was the only .err file that I have. Also this seems to be the latest entry that has a date. Thanks for your help. Gary 090617 21:50:45 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 133305 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 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... 006B8853mysqld.exe!??? The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 090627 9:35:07 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Carlos Proal carlos.pr...@gmail.com wrote in message news:4a58c79b.2090...@gmail.com... You have to take a look on the error log, a file named hostname.err (hostname=your machine name) and should be located inside the mysql dir on the xammp dir. That file will give an insight about the problem, or you can post the error here to get help. Carlos On 7/11/2009 11:52 AM, Gary wrote: I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: XAMMP-MySQL crashing
BTW, this is the error message that windows wanted to send to MS C:\DOCUME~1\GARYPA~1\LOCALS~1\Temp\WERd238.dir00\mysqld.exe.mdmp C:\DOCUME~1\GARYPA~1\LOCALS~1\Temp\WERd238.dir00\appcompat.txt Carlos Proal carlos.pr...@gmail.com wrote in message news:4a58cb7c.4090...@gmail.com... Take a look on the my.ini to see if there is something wrong (maybe a path pointing to a missing place or memory setting bigger than your actual RAM) Carlos On 7/11/2009 12:17 PM, Gary wrote: Not sure if this is it, it was the only .err file that I have. Also this seems to be the latest entry that has a date. Thanks for your help. Gary 090617 21:50:45 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 133305 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 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... 006B8853mysqld.exe!??? The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 090627 9:35:07 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Carlos Proal carlos.pr...@gmail.com wrote in message news:4a58c79b.2090...@gmail.com... You have to take a look on the error log, a file named hostname.err (hostname=your machine name) and should be located inside the mysql dir on the xammp dir. That file will give an insight about the problem, or you can post the error here to get help. Carlos On 7/11/2009 11:52 AM, Gary wrote: I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: XAMMP-MySQL crashing
sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] QueryInterval=10 Carlos Proal carlos.pr...@gmail.com wrote in message news:4a58cb7c.4090...@gmail.com... Take a look on the my.ini to see if there is something wrong (maybe a path pointing to a missing place or memory setting bigger than your actual RAM) Carlos On 7/11/2009 12:17 PM, Gary wrote: Not sure if this is it, it was the only .err file that I have. Also this seems to be the latest entry that has a date. Thanks for your help. Gary 090617 21:50:45 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 133305 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 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... 006B8853mysqld.exe!??? The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 090627 9:35:07 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Carlos Proal carlos.pr...@gmail.com wrote in message news:4a58c79b.2090...@gmail.com... You have to take a look on the error log, a file named hostname.err (hostname=your machine name) and should be located inside the mysql dir on the xammp dir. That file will give an insight about the problem, or you can post the error here to get help. Carlos On 7/11/2009 11:52 AM, Gary wrote: I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Crazy replication problem
Here is steps take to get to the problem. Version is 5.1.35 To be on the safe side, I did this prior to executing the scripts. I deleted the mysql directory and the mysql-log directory, recreated them, chowned them, ran mysql_install_db, chowned them again. So, I know at least I'm working with a clean setup. I did this on both servers. So, I have a script, which is actually a combination about 200 other scripts, cat'ed into a single script. The first half of the script creates a database and several tables. After all of the tables are created, a trigger is created on one of the tables. Following the trigger, about 150 stored procedures. This loads fine on the master server. All looks well. I then set the slave to slave against the master and it chokes on the creation of the trigger saying it can't find the table. quickly glancing at the tables, there are none. The database creation was replicated but the tables were not. Here is what I have in the my.cnf file for replication (we had this setup for master/master earlier, thus the replicate-do-db, but we will not be doing that in this round): log-slave-updates sync_binlog = 1 replicate-same-server-id= 0 log_bin_trust_function_creators = 1 log-bin = /exports/mysql-log/log-repl binlog-do-db= weblog binlog-do-db= webarchive replicate-do-db = weblog replicate-do-db = webarchive relay-log = relay-bin relay-log-index = relay-index relay-log-info-file = relay-info relay-log-purge = 1 Everything is INNODB. Any ideas as to why the tables didn't replicate? Also, I know that triggers shouldn't be replicated. Is there any was to prevent trigger replication? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Crazy replication problem
One quick follow up note: In the top of the big script, I'm doing: CREATE DATABASE x; CREATE TABLE x.whatever (...); Reading into the various bug reports, do I need to issue a USE x; ? I'm assuming that MySql isn't picking up the replica because there is no current database specified, even though the tables are qualified. Can anyone confirm this? Gary From: Gary Smith [g...@primeexalia.com] Sent: Friday, July 10, 2009 5:12 PM To: mysql@lists.mysql.com Subject: Crazy replication problem Here is steps take to get to the problem. Version is 5.1.35 To be on the safe side, I did this prior to executing the scripts. I deleted the mysql directory and the mysql-log directory, recreated them, chowned them, ran mysql_install_db, chowned them again. So, I know at least I'm working with a clean setup. I did this on both servers. So, I have a script, which is actually a combination about 200 other scripts, cat'ed into a single script. The first half of the script creates a database and several tables. After all of the tables are created, a trigger is created on one of the tables. Following the trigger, about 150 stored procedures. This loads fine on the master server. All looks well. I then set the slave to slave against the master and it chokes on the creation of the trigger saying it can't find the table. quickly glancing at the tables, there are none. The database creation was replicated but the tables were not. Here is what I have in the my.cnf file for replication (we had this setup for master/master earlier, thus the replicate-do-db, but we will not be doing that in this round): log-slave-updates sync_binlog = 1 replicate-same-server-id= 0 log_bin_trust_function_creators = 1 log-bin = /exports/mysql-log/log-repl binlog-do-db= weblog binlog-do-db= webarchive replicate-do-db = weblog replicate-do-db = webarchive relay-log = relay-bin relay-log-index = relay-index relay-log-info-file = relay-info relay-log-purge = 1 Everything is INNODB. Any ideas as to why the tables didn't replicate? Also, I know that triggers shouldn't be replicated. Is there any was to prevent trigger replication? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Crazy replication problem
-Original Message- From: Gary Smith Sent: Friday, July 10, 2009 5:26 PM To: Gary Smith; mysql@lists.mysql.com Subject: RE: Crazy replication problem One quick follow up note: In the top of the big script, I'm doing: CREATE DATABASE x; CREATE TABLE x.whatever (...); Reading into the various bug reports, do I need to issue a USE x; ? I'm assuming that MySql isn't picking up the replica because there is no current database specified, even though the tables are qualified. Can anyone confirm this? Gary So I found that adding a USE database to the tables allowed it to replicate. I vaguely remember something like that a couple years back. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication, Stored Proceedures and Databases
After getting table replication to work by including the USE database on the creation scripts, I have run into a rather large problem. We have 5 databases on the server which get replicated to another server. We call them databases, A, B, C, D, and E. we have two other databases F and G which are not replicated. Inside of F and G we have stored procedures (F for reading, G for writing) that are locked down pretty good. The stored procedures run as a specific user to do the task they need to do. The problem is when a procedure in G modifies a table in database A, the corresponding update isn't replicated to the slave database. We have some procedures that modify tables across the board and access other user functions inside of database G. In database G we have 150+ stored procedures. What's the best approach to fix this problem? Is it as simple as adding the appropriate USE statement inside of the stored procedure right before the insert/update/delete/whatever? Any help would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is there a way to disable SET PASSWORD for selected users
I would like to prevent users from changing their passwords unless they have
grant permission. Is this possible?
We have an set of stored procedures for managing users/databases for a single
signon project that we are working on. The problem is if a user connects
directly to the database using the client and changes their password using SET
PASSWORD= PASSWORD('their new password'), it breaks the single signon.
Thanks,
Gary
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
.Net provider question
Hello, I've run into an odd problem. I have a connection data management library for a project that we have been working with for a while. The purpose of this library was to work around some oddities in the ODBC environment where lots of connections were being held open for a long period of time. That's not really important though. Here's the problem. I maintain a list of open connections that are recycled. This is a pretty tight management list. It works great until you do something stupid like issue restart command to the MySql server at which time the connection is broken. What I know is that when this happens, the connection is goes from state=open to state=close. This is expected. The problem is that it has to cycle through each connection before the good ones are loaded. Is there a way to test a connection prior to using it to find out if it's actually open? Like pinging the active connection. If I could do something like that prior to returning the connection, I could then loop through the connections to check until I get a good one. We have all of the general login in place, but the assumption was made that the connection state was real time, which is now known to be false. Any ideas/assistance would be greatly appreciated. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: .Net provider question
Is there a way to test a connection prior to using it to find out if it's actually open? Like pinging the active connection. If I could do something like that prior to returning the connection, I could then loop through the connections to check until I get a good one. We have all of the general login in place, but the assumption was made that the connection state was real time, which is now known to be false. Any ideas/assistance would be greatly appreciated. As mentioned via the quick PM back... There is a ping. When I stop using IDBConnection and go with MySqlConnection there is a Ping() that doesn't exactly that. I guess I've been spending too much time on the MS and Oracle providers... Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: COUNT from 2 tables
Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0) 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month From: b [my...@logi.ca] Sent: Wednesday, July 08, 2009 12:21 PM To: mysql@lists.mysql.com Subject: COUNT from 2 tables I'm trying to get a count for columns in 2 tables at once. I have sessions and downloads tables and would like to get some basic stats. mysql describe sessions; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| +-+--+--+-+-++ 3 rows in set (0.01 sec) mysql describe downloads; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| | item_file_id | int(10) unsigned | NO | MUL | NULL|| | session_id | int(10) unsigned | NO | | NULL| | | path | text | NO | | NULL|| +--+--+--+-+-++ 6 rows in set (0.01 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM sessions GROUP BY month; +---++ | month | num_logins | +---++ | July | 6 | | June |214 | | May |150 | +---++ 3 rows in set (0.00 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads FROM downloads GROUP BY month; +---+---+ | month | num_downloads | +---+---+ | June | 389 | | May | 220 | +---+---+ 2 rows in set (0.01 sec) In trying to get the count from both tables at once, the logins are no longer being summed as expected: mysql SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins, COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month; +---++---+ | month | num_logins | num_downloads | +---++---+ | July | 6 | 0 | | June |539 | 389 | | May |350 | 220 | +---++---+ 3 rows in set (0.31 sec) Is this possible to do without using a sub-query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: COUNT from 2 tables
Create a view or sub select, denormalizing the data and then group it. select month, sum(login) as num_logins, sum(download) as num_downloads from ( select monthname(s.created) as month_name , if(ifnull(s.id, 0) 0, 1, 0) as login , if(ifnull(d.id, 0) 0, 1, 0) as download from sessions s left join downloads d on s.id = d.session_id ) group by month name; which is the left table? downloads or logins? If logins shouldn't it be on the left side of the ON statement? (I'm not sure) My understanding is that is the ON statement that runs the left join, not which table is listed first (I could be wrong though -- chime in if you know the definitive answer please). Anyway, try this and see if it gets you closer. From: b [my...@logi.ca] Sent: Wednesday, July 08, 2009 12:55 PM To: mysql@lists.mysql.com Subject: Re: COUNT from 2 tables On 07/08/2009 03:33 PM, Gary Smith wrote: Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0) 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month Nope, I'm still getting those same incorrect sums. Thanks, though. It seems to me that the problem is that I'm grouping by the month for one table but counting from both. I'd paste the output here but I just upgraded Fedora and the BETA (wtf?) version of Thunderbird crashes when I paste into an email (how the earlier paste worked I don't know). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Large insert question
Hello, I'm working on a project that will be inserting very large text streams into a database. They range from 100K to 100M. I suspect that the average will be about 2M per insert. This is a low volume (under 20 inserts per day). I don't really need to optimize much on this but I had a question regarding max data per insert. I know some time ago on another project I had to increase sometime to handle inserts over a certain size because of a default setting that limited the size of the data per connection. Anyone know what setting I need to tweak to ensure that it can accept large inserts of this size? Thanks, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Large insert question
Michael, Thanks. Thats what I was looking for, I just couldn't remember what it was. Gary From: Michael Dykman [mdyk...@gmail.com] Sent: Wednesday, May 20, 2009 9:17 AM To: Gary Smith Cc: mysql@lists.mysql.com Subject: Re: Large insert question On Wed, May 20, 2009 at 12:02 PM, Gary Smith g...@primeexalia.com wrote: Hello, I'm working on a project that will be inserting very large text streams into a database. They range from 100K to 100M. I suspect that the average will be about 2M per insert. This is a low volume (under 20 inserts per day). I don't really need to optimize much on this but I had a question regarding max data per insert. I know some time ago on another project I had to increase sometime to handle inserts over a certain size because of a default setting that limited the size of the data per connection. Anyone know what setting I need to tweak to ensure that it can accept large inserts of this size? Thanks, Gary As I recall, max_allowed_packet is what controls that limit. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problems After MySql 5.1.34
Typically we see the problems with RH/Cent when you upgrade through those channels and then do an install of the 5.1.x series on top of that. I tried it about 9 months ago, so the details are fuzzy. I do know that in attempts to recover we extracted the RPM contents and tried to copy them manually to satisfy the dependency but in the end that didn't work for us for some reason. We ended up just creating a new RPM for 5.1.x and then recompiling the dependent apps against that and storing all of them in our own repo for yum update. From: joerg.bru...@sun.com [joerg.bru...@sun.com] Sent: Thursday, May 07, 2009 2:40 AM To: Gary Smith Cc: mysql@lists.mysql.com Subject: Re: Problems After MySql 5.1.34 Hi Gary, all, Gary Smith wrote: Johnny, Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Which other approach(es) did you try? This is why you are receiving the error through PHP. Of course, I could be wrong, in which case I know people will probably jump me for it. If this is the case, please do as I would like to be wrong here as it would make my compiling life easier every time I update MySql on all of my boxes. Please see my other mail: - Using tar.gz, the old version of the client libs should not be touched when you upgrade MySQL. - Using RPMs, you need to install shared-compat (and not shared) if you are using application binaries built against older version(s). If you found any problems with this, please tell us! We are considering to change the contents of shared-compat RPMs, so that they would not replace shared any more but just complement it (bring just the old libs, not the current one). The advantage would be that you then can install (or uninstall) them without affecting your clients built against the current version, especially those coming from MySQL directly. However, this would mean a change in usage pattern for existing users, and we are not yet sufficiently sure that our users would welcome it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problems After MySql 5.1.34
You are right. I misspoke regarding mysql - php - apache hell. It happens anytime an interface changes. From: Mark [ad...@asarian-host.net] Sent: Thursday, May 07, 2009 5:57 AM To: mysql@lists.mysql.com Subject: RE: Problems After MySql 5.1.34 Gary wrote: Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Well, you don't actually have to recompile PHP entirely, of course: just its mysql.so extension. @TS: Other than that, you basically need to recompile *everything* (or its mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw; so you'd need to build DBD:mysql as well (same for Python, etc). Walter wrote: Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. Actually, you *do* know: that's what the changelog is for. :) When C header changes are made, an upgrade is in order. If not, when upgrading between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you won't need to recompile all system-wide MySQL client extensions. I've done this many times, without issue: you just need to be absolutely sure no header changes were made (when in doubt, recompile). I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's working just fine, but I spent several hours recompiling MySL client stuff; without doing so, your apps will likely behave erratically, or just segfault altogether. This isn't a MySL hell exclusively, btw. You'll get the same issue upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the beast. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problems After MySql 5.1.34
Johnny, Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. This is why you are receiving the error through PHP. Of course, I could be wrong, in which case I know people will probably jump me for it. If this is the case, please do as I would like to be wrong here as it would make my compiling life easier every time I update MySql on all of my boxes. Gary From: Johnny Stork [li...@openenterprise.ca] Sent: Wednesday, May 06, 2009 1:03 PM Cc: mysql@lists.mysql.com Subject: Re: Problems After MySql 5.1.34 Typo, moved from 5.0.67 to 5.1.34 Johnny Stork wrote: I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 5.64. Access to the db seesm fine from the shell, phpmyadmin or even the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or running a pear update produces the errors below. I created a couple of sl but this did not seem to fix the problem. Below is the error and contents of /usr/lib r...@asterisk:~# pear update PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysql.so) in Unknown on line 0 PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysqli.so) in Unknown on line 0 Segmentation fault r...@asterisk:~# ls -la /usr/lib/libmy* lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 28 May 6 11:26 /usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 28 May 6 11:18 /usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0 -rwxr-xr-x 1 root root 2052884 Mar 31 22:48 /usr/lib/libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so - libmysqlclient.so.16.0.0 lrwxrwxrwx 1 root root 26 May 6 11:14 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0 -rwxr-xr-x 1 root root 2044464 Mar 31 22:48 /usr/lib/libmysqlclient.so.16.0.0 -- _ Johnny Stork Open Enterprise Solutions Empowering Business With Open Solutions http://www.openenterprise.ca Mountain Hosting Secure Hosting Solutions for Business http://www.mountainhosting.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Where the hell did 5.4 come from?
Oracle owns the mess now. I assume the next release will be 5.11, followed by 5.11i, and then finally dropping of the 5 to be in line with how they manager their os, leave it to just be 11i. To ensure it is smooth they will change the license and add $5k in suport costs. From: Andy Shellam [andy-li...@networkmail.eu] Sent: Thursday, April 30, 2009 2:19 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Where the hell did 5.4 come from? My thoughts exactly! This article might help: http://dev.mysql.com/tech-resources/articles/mysql-54.html It worries me though that 5.1 went through a large number of alpha releases, then a set of beta releases before the GA release came out. It looks like they've thrown 5.4 straight out without anyone even being aware that it existed! Hell, 6.0 is on its tenth release and it's still in alpha. Like you say, it'd be interesting to see which blackhole 5.2 and 5.3 fell into...! Andy Daevid Vincent wrote: Have I been in a coma or something? WTF happened to 5.2 and 5.3? Hell, we're still on 5.0.51 and 5.1 just came out a month or two ago right? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 30, 2009 7:40 AM To: mysql@lists.mysql.com Subject: Is there a MySQL 5.4 Speed advantage for MyISAM tables? I see MySQL 5.4 is out. http://www.mysql.com/news-and-events/generate-article.php?id=1602 Sun claims there are speed improvements for Innodb and ClusterDb tables, but is there any reason to upgrade if I'm only using MyISAM tables? Also I didn't see a Windows binary download. Does this mean I have to compile the source from one of the Linux distros? What compiler do I use? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Partition of Mysql
Lin, I've had mixes results but you might have better success. As John mentioned, there are a couple factors that you need to take into account. How much data are you talking about (physical size and number of rows). I know you say 15 years of data but is that 100's of millions of rows? Give you more information and we can probably give you a better answer. Maybe a table structure and numbers behind it would be nice. Gary From: Lin Chun [franks1...@gmail.com] Sent: Wednesday, April 29, 2009 7:49 AM To: mysql@lists.mysql.com Subject: Partition of Mysql Hi I have a table that stores huge rows in 15 years, now we have to do some analysis about these row in Time dimension.To gain performance,at begining, i've extrait the data according to years from my DB to build my fact table. But since I have 15 fact tables, that complicate my olap cube.Today, i found that mysql surpport partition table since 5.1, i wanna know is it work well ? I mean that is it more efficace than table with-out partitions? I don't want to transformer the data again, as it takes too much time, is it possible to just alter my orignal table to add the partitions by years, excuse my poor english writing and thank you for your answers -- - Lin Chun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
flush-host problem
I have system that is generating a larger than normal number of connection errors. We know why the errors are occuring and are working to resolve them (connectivity and load issue on the client). The question is, how can I tweak mysql to tolerate a higher level than normal of bad connections before banning the host. What happens is that when we have 300-500 connections a few random ones will get mucked up during a heavier than normal load on the client. I have set the max connections to 3000 (which we never get close to). So, if there a config/startup setting to tweak to ease the banning of bad connetions thus reducing the need for me to continually mysqladmin flush-host on the server? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: flush-host problem
Mike, It's not a connection pooling issue per say. We have several boxes running spam assassin, sqlgrey and postfix (via mysql). Normally these components work great. SA and sqlgrey both have a fixed number of connections, around 16, that they are generally actively using unless we get a burst of email, at which time they will increase by a few. The problem is that Postfix has been receiving a higher level than normal of emails as we have taken 50% of our servers offline at this location (setting them up at a new location). We're also have this bouncing across a couple different firewalls, so for some reason, the conneciton to mysql is generating a larger number of these: 090407 12:26:42 [Warning] Aborted connection 972479 to db: 'db' user: 'user' host: 'host' (Got an error reading communication packets) We do know the network isn't optimal right now and are working to fix the issues but we are hoping to get by just for the short term. But that leads back to the original question about increase the connection error cutoff before banning a host. We are using 5.1.32 with INNODB tables. From: mos [mo...@fastmail.fm] Sent: Tuesday, April 07, 2009 9:18 AM To: mysql@lists.mysql.com Subject: Re: flush-host problem At 10:39 AM 4/7/2009, Gary Smith wrote: I have system that is generating a larger than normal number of connection errors. We know why the errors are occuring and are working to resolve them (connectivity and load issue on the client). The question is, how can I tweak mysql to tolerate a higher level than normal of bad connections before banning the host. What happens is that when we have 300-500 connections a few random ones will get mucked up during a heavier than normal load on the client. I have set the max connections to 3000 (which we never get close to). So, if there a config/startup setting to tweak to ease the banning of bad connetions thus reducing the need for me to continually mysqladmin flush-host on the server? -- What do you mean mucked up? This is a technical term I'm not familiar with. :-) Do you mean a few of the queries are taking too long to complete? You could do a Show ProcessList every 15 seconds and kill the process for the low level user that is taking too long, say over 30 seconds. I'm sure there are also monitoring tools that could do this for you. If you are using transaction-less MyISAM tables, can you not use connection pooling? We've done that in Delphi and MySQL and the connections have dropped considerably. I'm not sure what language you're using or if this is a webserver, but a Google search on your development language and MySQL connection pooling should get you pointed in the right direction. Also you did not say what version of MySQL you're using. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Search based where claused and stored proc
I'm working on a small project of re-implementing all of the sql for a web site. The task is pretty trivial but overall there are some minor things that I'm trying to code through. We've moved much of the logic over to stored procs and call them with parameterized queries. This works well since there isn't much inject attack possibility on these. Now I have one query left, which allows for an arbitrary number of search parameters, all using AND. Has anyone accomplished coverting something like this to a stored proc in mysql? Logically I could pass in the parameters in as an array of words, or a wordlist to be broken up inside the proc, but I don't want to spend a bunch of time either reinventing the wheel or working to a goal that can't be accomplished. We could build the base query dynamically in the code using standard sql and bind the parameters to it that way but since we've moved everything else to procs I figured I'd look into this as well. BTW, this is a project I brought onto after they found they had a sql injection bug in there code that was exploited... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Search based where claused and stored proc
There was an article on forums.mysql.com (save the content, not the link) that takes, as the in parameter, a comma delimited list of values and then breaks them down and inserts them into a temp table. The article uses and innodb table, which I find sort of odd as a memory table would probably be faster. It uses this to join it to the table/columns that the search is being conducted on. This seems to be the most common approach I've found. The problem with this approach is that I don't know how to join a table against a column when you are looking for like data instead of equality. This gets me closer, but not exactly there yet. From: Martin Gainty [mgai...@hotmail.com] Sent: Friday, March 27, 2009 1:58 PM To: b...@wisper-wireless.com; mysql@lists.mysql.com Subject: RE: Search based where claused and stored proc Ben- did'nt see your solution? Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. From: b...@wisper-wireless.com To: mysql@lists.mysql.com Subject: RE: Search based where claused and stored proc Date: Fri, 27 Mar 2009 13:43:51 -0500 Ben Wiechman Network Administrator Wisper High Speed Internet Office: 866.394.7737 Direct: 320.256.0184 Cell: 320.247.3224 b...@wisper-wireless.com -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Friday, March 27, 2009 12:59 PM To: mysql@lists.mysql.com Subject: [MySQL] Search based where claused and stored proc I'm working on a small project of re-implementing all of the sql for a web site. The task is pretty trivial but overall there are some minor things that I'm trying to code through. We've moved much of the logic over to stored procs and call them with parameterized queries. This works well since there isn't much inject attack possibility on these. Now I have one query left, which allows for an arbitrary number of search parameters, all using AND. Has anyone accomplished coverting something like this to a stored proc in mysql? Logically I could pass in the parameters in as an array of words, or a wordlist to be broken up inside the proc, but I don't want to spend a bunch of time either reinventing the wheel or working to a goal that can't be accomplished. We could build the base query dynamically in the code using standard sql and bind the parameters to it that way but since we've moved everything else to procs I figured I'd look into this as well. BTW, this is a project I brought onto after they found they had a sql injection bug in there code that was exploited... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=b...@meltel.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Express your personality in color! Preview and select themes for Hotmail®. http://www.windowslive-hotmail.com/LearnMore/personalize.aspx?ocid=TXT_MSGTX_WL_HM_express_032009#colortheme -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Please help me.
Velentin, http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html Note the section for the droping of foreign keys used the contraint name, not the key name. Try this and see if it solves the first problem (of removing the constraint). Then you should be able to drop the column after that. From: Valentin Ionescu [colibry...@yahoo.com] Sent: Wednesday, March 18, 2009 11:27 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Please help me. Hi! My name is Valentin and I am writing to you for the following problem: I created a database containing the table: CREATE TABLE `documents_ex` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Journal_ex_ID` int(10) unsigned DEFAULT NULL, `Documents_ID` int(10) unsigned DEFAULT NULL, `Data` datetime DEFAULT NULL, `Nr` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Journal_ex_ID` (`Journal_ex_ID`,`Documents_ID`,`Data`,`Nr`), KEY `Documents_ID` (`Documents_ID`), CONSTRAINT `documents_ex_ibfk_1` FOREIGN KEY (`Journal_ex_ID`) REFERENCES `journal_ex` (`ID`) ON DELETE CASCADE, CONSTRAINT `documents_ex_ibfk_2` FOREIGN KEY (`Documents_ID`) REFERENCES `documents` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After some time I decided to drop 'Journal_ex_ID' column and all its environment from this non empty table. All I tried to do like: alter table documents_ex drop column Journal_ex_ID or alter table documents_ex drop foreign key Journal_ex_ID or alter table documents_ex drop key Journal_ex_ID I receive the same error 150 and I don't know what to do. Please help me. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
Being rather new to all this, I understood from the MySql manual that the auto_increment is to b e used immediately after an insertion not intermittently. My application is for administrators (the site owner designates) to update the database from and administration directory, accessed by user/password login... so there's really very little possibility of 2 people accessing at the same time. By using MAX + 1 I keep the id number in the $idIn and can reuse it in other INSERTS -- The statement is confusing at best. For the casual user auto_increment is the way to do. I say for the casual user. That is typical me and you. Basically if you do an insert a unique value is inserted at the time of the insert. As mentioned, there are ways to get this value back in the return. Now why I say it's for the casual user is because if you are using triggers then you can do things prior to this value being used and then the statement above is correct. But you are not going to be using triggers... So, put an auto_increment on the key field and find one of the 2^16 samples of how this works with PHP. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Upgrade story / request for insight
Jerry, To touch a little more on Claudio's statement, you are trying to compare monkey's and trucks when you talk about mysql on these two different OS's. Microsoft is a different best when it comes to the install. What caught my attention though is you are running mysql 4.0 on CentOS. This means that you are probably running an older version of CentOS as 5.x comes with mysql 5.0 (I believe). You might want to setup a similar environment with the same OS and do a db upgrade on that (without your actual data) and see if everything works first. You might find some lib issues with the older CentOS. Gary From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Wed 2/25/2009 12:50 PM To: Jerry Schwartz Cc: MySql Subject: Re: Upgrade story / request for insight Hi Jerry, probably does not help you very much and excuse me in advance for this, but there is little use in having a development/preproduction system on different architecture, none of the issues you faced with windows (services installation probably) will show up on a CentOS box. In particular an upgrade which involves filesystem and services installation is quite different between Win and Linux. From a 'service' point of view (MySQL server) there will be no difference for any client in accessing a Win or a Linux box, but from a maintenance point of view you are facing problems that are peculiar of the platform, in windows in fact mysql is installed as a service so you should check windows services as well. In any case I strategy I always used for migration is to install the new version and export / import data, this is good because you have two parallel servers up and you can compare and test both of them, provided you are using different 'sockets', that is different PORT if just using TCP/IP connection method. Cheers Claudio Nanni 2009/2/25 Jerry Schwartz jschwa...@the-infoshop.com My ultimate goal is to upgrade a production server (MySQL 4.1.22 on CentOS) to a modern 5.1 release. My development system is a Windows Vista x86 machine, and although the process is not that similar I decided to try an upgrade there. (I've never done one.) I figured this would give me some insight as to whether or not our code would break. The upgrade from 5.0.45 to 5.1.31 was a horror show! I downloaded the 5.1.31 msi package, and ran the wizard. The Windows notes seemed to say that for this upgrade I didn't need to uninstall the old one, and that might have been a mistake. In any case, the wizard attempted to install 5.1.31, but after it asked me if I wanted to configure an instance it just disappeared. I ran the instance configuration wizard by hand, and it showed two different server versions. The older one was apparently still running. I tried shutting it down; I tried deleting it with the sc command, which (after a reboot) did make it go away; but the instance configuration wizard still listed it. In fact, it still listed it after I renamed the MySQL 5.0 directory. The 5.1 server would attempt to start, but would fall over dead immediately. I uninstalled 5.0, and that made no difference. I uninstalled 5.1, and when I reinstalled it I got the same basic behavior. Eventually I went through the registry and wiped out every reference to MySQL that I could find. After a reboot and one last installation of 5.1, things started to work right. From there on I was able to run mysql_upgrade and get myself back on the air. Can anyone guess where I went astray? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com
RE: non-auto increment question
Not sure that this is the problem BUT you should probably qualify the name of
the variable such that SELECT MAX(id) AS id FROM book. But you don't want
max(id) as id but rather max(id) + 1 as id. With that you can then just
return the final value. Also, if you don't want to alias the value (or
whatever it's called) you should use $row[0] to get it by ordinal posistion.
As for now wanting to use autoincrement, you can run into a race condition
where two people are inserting at the same time, thus having the same generated
id.
Hope that helps.
From: PJ [mailto:af.gour...@videotron.ca]
Sent: Wed 2/25/2009 2:01 PM
To: MySql; php-gene...@lists.php.net
Subject: non-auto increment question
I want to insert a new table entry 1 number higher than the highest in
the field (id). I cannot use auto-increment.
And I want to show the value of the field to be added in an input field
on the web page:
if (isset($_REQUEST[AddNewBooksRequest])) {
$SQL = SELECT MAX(id) FROM book;
$result = mysql_query($sql, $db);
$bookCount = mysql_num_rows($result);
for ($i=0; $i $bookCount; $i++) {
$row = mysql_fetch_array($result);
$idIN= $row[id]+1;
}
$idIN= $_POST[idIN];
$titleIN= $_POST[titleIN];
...snip...
td colspan=2
?
echo input type='text' name='titleIN' value='$idIN' disabled size='2';
?
/td
What am I doing wrong? (The query works and returns the right nr. but
what do I have to do to add 1 to that number and then display it in the
on page and post it to the table?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com http://www.ptahhotep.com/
http://www.chiccantine.com http://www.chiccantine.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com
INNODB and Max Processors
A few weeks back I was reading an article that said that INNODB doesn't take adantage of servers using more than 4 processors. I think I also recieved this as a reply some time ago as to the same thing. I was wondering if this is indeed true. We are using 5.1.30 and wanted to pickup a new dual quad core with 32GB. Before we make the purchase we just want to make sure the database will be able to take advantage of it. Otherwise we will go for the dual core higher speed. This will support hundreds of connections per second and some complicated queries. Overall the data will be less than 50gb so we are looking at more ram to hope that it will support both application and os level caching. Any advice would be greatly appreciated. Gary
RE: Compare DATETIME to DATE
Truncate the time part of the datetime field when doing the compare AND DATE_FORMAT(customer.created_dt, '%Y-%m-%d 00:00:00') BETWEEN '2008-12-30' AND '2008-12-30' Should work. Probably not the most efficient. The other options would be to use take end date + 1 day, minue 1 second. That's even a bigger hack but it would probably be more efficient than converting all of the dates on the fly if you have a large number of records to process. From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wed 12/31/2008 10:13 AM To: MySQL General List Subject: Compare DATETIME to DATE Hi, I don't quite understand (or even know) what the proper way to compare a DATETIME column to a given DATE value is. I've used various methods but I'd like to know if there's a better way to compare these values. Right now I have a query with this in the WHERE clause (customer.created_dt is a DATETIME): AND CAST(customer.created_dt AS DATE) BETWEEN '2008-12-30' AND '2008-12-30' This was working (MySQL on Win32) before I moved the database to MySQL on RHEL 64-bit (5.0.45-log). Should that work? I've also done this: AND customer.created_dt BETWEEN '2008-12-30 00:00:00' AND '2008-12-30 23:59:59' That works on both servers, but I really don't want to have to put the time in there (unless that's the way you are supposed to do this). I've though about using DATE_FORMAT... not sure about that either. - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: too many connections
Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Failed auth loggin
Hello, I've been looking through the documentation/list and haven't found anything directly on this subject. It's possible that I'm just not looking in the right place. I would like to log all failed authentications to the server. It would be nice to be able to log the attempted user name, host, date/time. Is there anything like this already in MySQL? Gary
Re: order of items in a WHERE...IN clause
Andrew Martin wrote: Hello, Is it permissible to order a clause such that the search term is the first item (in the clause)? standard: field1 IN (123, 654, 789) in question: 123 IN (field1, field2, field3) I am interested to know if the optimizer treats this any differently if anybody can shed any light on it (except for the obvious difference in the above queries!) Thanks, Andy Both are valid syntax where 1 is returned if the expression is equal to any of the values in the list. I can't see the optimizer treating these any differently. Thanks, Gary M. Josack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aggregation question
I have a table that stores performed transactions and I need to build a histogram of a number of transactions per day in the requested period. So, I made a simple query with the group by clause which returns me what I need: 2008-04-16 65456204 2008-04-17 190838546 2008-04-18 8909047 2008-04-19 9085084 2008-04-21 18221038 2008-04-22 18246184 except that there is no entry for April 20th as there were no transactions at that day. I need a query to return me zero for that day. I.e. I need uninterrupted sequence of dates. I am beating my head at this problem for the whole day and did not make much of a progress. If someone has any idea how to resolve this problem, I'll appreciate a tip greatly. Thank you, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Warehouse on MySQL questions
I've built several datamarts using perl and MySQL. The largest ones have been up to about 30GB, so I'm not quite on your scale. for #1, I have an etl_id in the fact table so I can track back any particular ETL job. I typically make it a dimension and include date, time, software version, etc. That doesn't help so much if you're messing up your dimension tables, but I haven't typically run into that problem based on the designs I've used. For #2, I haven't built anything big enough for it to be a concern yet.. Also, LOAD DATA INFILE is your friend :) On Thu, Apr 3, 2008 at 11:28 AM, Dre [EMAIL PROTECTED] wrote: Hey folks, I'm currently deciding whether to build a decent sized (around 300-500GB, although honestly, I've got little to base that on at the moment) data warehouse in postgreSQL or MySQL. I've developed several in MS SQL and postgreSQL, but the client is comfortable with MySQL, and I'd prefer to use that as the platform since it will be less painful for them to manage when I'm gone. I'm hoping that someone with experience building a warehouse on MySQL will be able to answer two outstanding questions I have: 1) Several sources seem to suggest MyISAM is a good choice for data warehousing, but due to my lack of experience in a transaction-less world, this makes me a little nervous. How do you handle data inconsistency problems when ETL jobs fail? (For the record, I don't use a separate tool for the ETL; I usually use perl/shell scripts to interact with the file system, and pl/pgsql or transact-sql once the data is loaded into the staging database. For each file that is loaded, I'll identify steps that must be posted together, and wrap them in a transaction in the ETL job.) I can see doing something like manually cleaning out the necessary tables before you re-run, but that seems a bit messy to me. Anyone figure out a better approach? 2) Isn't the lack of bitmap indexes a problem in the warehouse? Most FKs in the fact tables will be low cardinality columns; queries that didn't use date would be very slow on large fact tables (MS SQL had this problem). Has anyone run into this with MySQL? Many thanks in advance! -- 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: Column level replication q?
Michael, It seemed to replicate just fine. At least in test. (5.1.22RC). I believe that we are using statement level replication which might be triggering the trigger on the slave node. I noticed that the triggers themselves also replicatated to the slaves, which is something I didn't expect. Gary From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Thu 2/21/2008 8:06 AM To: mysql@lists.mysql.com Cc: Gary W. Smith; Baron Schwartz Subject: Re: Column level replication q? On Wed, Feb 20, 2008 at 10:48 AM, Gary W. Smith [EMAIL PROTECTED] wrote: Well, I created the lookup table, created my two triggers (as nothing is ever updated, just added or removed) and did a bulk one time load and it seems to work. Now I just need to replicate that to the other server. I'll have to find my easy button and press it a few times. In fact, on the other end, we don't even need to have the same table names. So we can replicate it just like this with no problem. Thanks for the links, I am curious: I thought that data written by triggers was not replicated, replicating instead the action to call the trigger. I seem to recall that several approaches to logically replicate triggers have been tried, where are we at these days? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful.
RE: Column level replication q?
Claudio, I didn't think about that approach either. The triggers will be much simpler to implement on the primary servers, then pull it over with a table rewrite to the intermediate server, then allow that to replicate out just fine. I'll play around with it a little. The table has millions of rows, but the primary data in the table that I really care about is relationship keys, which should be small if we put just that data into a intermediate table. Thanks, Gary Wayne Smith From: Nanni Claudio [mailto:[EMAIL PROTECTED] Sent: Wed 2/20/2008 1:19 AM To: Gary W. Smith; mysql@lists.mysql.com Subject: R: Column level replication q? Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person.
RE: Column level replication q?
Well, I created the lookup table, created my two triggers (as nothing is ever updated, just added or removed) and did a bulk one time load and it seems to work. Now I just need to replicate that to the other server. I'll have to find my easy button and press it a few times. In fact, on the other end, we don't even need to have the same table names. So we can replicate it just like this with no problem. Thanks for the links, Gary From: Nanni Claudio [mailto:[EMAIL PROTECTED] Sent: Wed 2/20/2008 1:19 AM To: Gary W. Smith; mysql@lists.mysql.com Subject: R: Column level replication q? Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person.
Column level replication q?
We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith
RE: Column level replication q?
And that is a hack at best, but it does give me some ideas. I really only need two fields out of that table anyhow so I might actually migrate the columns that I care about into a new table and update the corresponding SQL statements that I use to update them (i.e. split the source data). From: [EMAIL PROTECTED] on behalf of Baron Schwartz Sent: Tue 2/19/2008 5:15 PM To: Gary W. Smith Cc: mysql@lists.mysql.com Subject: Re: Column level replication q? Hi, This isn't natively supported. You can hack it with replication to a table that has a trigger, which will then insert all but one column into another table, which you can replicate on to the final destination. But I'm scared of such hacks for anything that matters :-)
Replication and changing engine type
We have a master/master environment that has tables in innodb. We want to setup a slave that will be used for reporting and some other stuff as well. We want to use MyISAM as the engine on the slave'd server. Since all of the table creations are also part of the replication, is it possible to override the table creates and force them to use a different engine? I have read a few articles on implementing the blackhole engine (for intermediate replication) which would be useful for us when we setup the replication to multiple sites, but this leads to the same question of how to change the engine (which isn't explained in the sample articles I've read). Any help would be greatly appreciated. Gary Wayne Smith
RE: Replication and changing engine type
You can set the default storage engine on each of the servers and then don't declare it explicitly in any CREATE TABLE statements. This seems like the most viable option. Since almost all of the remote tables are created with INNODB it should work fine. I do have one table that isn't but we will convert that before we slave the data to this box. The big problem is test existing dataset is more than 10gb (across several tables/databases). So other suggestions to just recreate the tables by not specifying the engine type isn't particle at this time (but if done at initial design time, we would have been fine). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure host and user name for non static ip address
Sign up for dyndns.com or some other similiar service. Create permissions to the domain and run a script that updates you IP with dyndns whenever it changes. (such scripts already exist). As far as 'username'@'%' with no password with SELECT, INSERT, UPDATE and DELETE privileges... worst idea ever in my opinion. Anyone that knows your server/username can get in query and delete records. Stephen Sunderlin wrote: QUESTION: What are the most secure permissions settings for administrator access to connect to my server without using a static IP address? MY ISP changes my DSL ip address almost daily so when I log on to MySQL Administrator with 'myusername'@'currentipaddress' using password 'mypassword' I have grant permission to the new ip address. I also have and account: 'username'@'%' with no password with SELECT, INSERT, UPDATE and DELETE privileges only for general users for this membership site. Are there any security issues with this? Any input or direction for informed reading on the issue would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: funky characters in columns
Try:
replace(replace(dealerLong, '\n', ''), '\r', '')
Jay Blanchard wrote:
I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;
mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
|.9040
++
(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.
For troubleshooting purposes, once I had narrowed down the problem
column I did the following
mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
| |
+--+
You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.
update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';
has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.
Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Daniel Kasak wrote: Greetings. I've just returned from holidays, and it seems that all but 1 ibdata file ( there were 10! ) have been deleted by a co-worker. He apparently was able to delete them with nautilus ( he was looking to reclaim some space and these were 1GB files each ... and yes, the Trash was emptied as well ). I would have assumed these would be protected from being deleted while mysql was up, but this clearly wasn't the case ( or was it? perhaps they are still around? ). The astonishing thing is that mysql doesn't seem to care about this. It happened 1 week ago, and no-one has complained about any database-related problems since. I've got a nightly backup script which does a 'mysqldump' on each database, and then restarts the server ( which gives me daily transaction logs ). Tonight, I obviously plan on doing a complete reinstall from an old backup, and running the transaction logs. But, just out of curiosity ... what the hell is going on? Why is mysql not complaining bitterly, crashing, and worse? Note the lack of ibdata1 ... ibdata9 screamer mysql # ls -l total 2885424 drwx-- 2 mysql mysql 480 Nov 26 2006 Assets drwx-- 2 mysql mysql 168 Sep 21 14:57 EPricing drwx-- 2 mysql mysql 12512 Sep 25 21:04 NUS drwx-- 2 mysql mysql 656 Nov 26 2006 Timekeeper drwx-- 2 mysql mysql 1216 Jul 4 08:46 dbmail drwx-- 2 mysql mysql 648 Aug 13 14:18 dspam drwx-- 2 mysql mysql 16584 Sep 18 09:32 ebills drwx-- 2 mysql mysql 824 Sep 24 14:45 energy -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile1 lrwxrwxrwx 1 root root 15112077312 Sep 26 09:08 ibdata10 drwx-- 2 mysql mysql 1848 Nov 26 2006 mysql drwx-- 2 mysql mysql 648 Dec 20 2006 roundcubemail drwx-- 3 mysql mysql 6240 Sep 26 08:54 sales -rw-rw 1 mysql mysql 239845598 Sep 14 20:40 screamer-bin.000201 -rw-rw 1 mysql mysql 591774120 Sep 17 20:44 screamer-bin.000202 -rw-rw 1 mysql mysql 289226895 Sep 18 20:38 screamer-bin.000203 -rw-rw 1 mysql mysql 281415790 Sep 19 20:36 screamer-bin.000204 -rw-rw 1 mysql mysql 286245722 Sep 20 20:37 screamer-bin.000205 -rw-rw 1 mysql mysql 331061564 Sep 21 20:43 screamer-bin.000206 -rw-rw 1 mysql mysql 411650198 Sep 24 20:38 screamer-bin.000207 -rw-rw 1 mysql mysql 309576301 Sep 25 20:37 screamer-bin.000208 -rw-rw 1 mysql mysql 140007700 Sep 26 09:08 screamer-bin.000209 -rw-rw 1 mysql mysql 3520 Sep 25 20:37 screamer-bin.index -rw-rw 1 mysql mysql 60432327 Sep 26 09:06 screamer-slow.log drwx-- 2 mysql mysql80 Sep 20 11:16 test screamer mysql # The only way I can explain the fact that things are still working is that these files are in fact *not* deleted, and are still in use by mysql, but not visible to anything else. But that's ridiculous as well. So what's going on? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Daniel Kasak wrote: On Tue, 2007-09-25 at 19:27 -0400, Gary Josack wrote: Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Thanks for the quick response :) They're there: mysqld 5460 mysql 10uW REG8,3 1073741824 761001 /root/.Trash/ibdata2 (deleted) mysqld 5460 mysql 11uW REG8,3 1073741824 6852461 /root/.Trash/ibdata3 (deleted) mysqld 5460 mysql 12uW REG8,3 1073741824 7376938 /root/.Trash/ibdata4 (deleted) mysqld 5460 mysql 13uW REG8,3 1073741824 6859981 /root/.Trash/ibdata5 (deleted) mysqld 5460 mysql 14uW REG8,3 1073741824 7376491 /root/.Trash/ibdata6 (deleted) mysqld 5460 mysql 15uW REG8,3 1073741824 7376500 /root/.Trash/ibdata7 (deleted) mysqld 5460 mysql 16uW REG8,3 1073741824 1369981 /root/.Trash/ibdata8 (deleted) mysqld 5460 mysql 17uW REG8,3 1073741824 7377058 /root/.Trash/ibdata9 (deleted) ( sorry about the text wrapping thing ) How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
