In MySQL 8, how do you distinguish between Roles and Users in table mysql.user?
Hi there, In MySQL 8, how can you figure out if an entry in the mysql.user table is a role or a user? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.
Re: commands out of sync, you can't run this command now'
Am 08.01.2016 um 10:14 schrieb Nitin Mehta: Looks like some kind of locking mechanism in the application. Should not be a database issue. no true - this is a native mysql error message! honestly both of you should have used Google http://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html http://stackoverflow.com/questions/15798762/mysqli-commands-out-of-sync-you-cant-run-this-command-now On Friday, January 8, 2016 10:47 AM, geetanjali mehra <mailtogeetanj...@gmail.com> wrote: Hi all, Suddenly I have started getting below error from our web site: 'commands out of sync, you can't run this command now' This error is generated sometimes when opening a page ,and immediately after refreshing the page, error gone. Can you please help me to troubleshoot this issue, so that above error disappears permanently? Is it a database issue or application side issue? signature.asc Description: OpenPGP digital signature
Re: commands out of sync, you can't run this command now'
On 1/8/2016 3:14, Nitin Mehta wrote: Looks like some kind of locking mechanism in the application. Should not be a database issue. On Friday, January 8, 2016 10:47 AM, geetanjali mehra <mailtogeetanj...@gmail.com> wrote: Hi all, Suddenly I have started getting below error from our web site: 'commands out of sync, you can't run this command now' This error is generated sometimes when opening a page ,and immediately after refreshing the page, error gone. Can you please help me to troubleshoot this issue, so that above error disappears permanently? Is it a database issue or application side issue? http://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html PB - Best Regards, Geetanjali Mehra Senior Database Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
commands out of sync, you can't run this command now'
Hi all, Suddenly I have started getting below error from our web site: 'commands out of sync, you can't run this command now' This error is generated sometimes when opening a page ,and immediately after refreshing the page, error gone. Can you please help me to troubleshoot this issue, so that above error disappears permanently? Is it a database issue or application side issue? Best Regards, Geetanjali Mehra Senior Database Administrator
RE: [Suspected Spam][Characteristics] RE: Slow Response -- What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections
to be blocked. Solution: switch to InnoDB. Caution: There
are a few caveats when switching; see
https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
2. As mentioned by Shawn, the Query Cache can be more trouble
than it is worth. However 90 seconds cannot be blamed on the
QC. Still, shrink it or turn it off:
* If frequently writing to tables, turn it off (type=OFF _and_ size=0)
* If less frequently, then decide which queries will benefit,
add SQL_CACHE to them, set type=DEMAND and size=50M (no larger).
3. Meanwhile, try to make that long query more efficient.
Can you show it to us, together with SHOW CREATE TABLE, SHOW
TABLE STATUS, and EXPLAIN ?
Thanks for the feedback, Rick.
There are 1200+ tables in the database, so I don't think you want a SHOW CREATE
TABLE, SHOW TABLE STATUS, EXPLAIN for all of them. :-) The query in question is
always some variation of the following. From looking at this, which table(s)
would you like to see this information for?
# Time: 130507 18:14:26
# User@Host: site150_DbUser[site150_DbUser] @ cognos08.mycharts.md
[192.168.10.85]
# Query_time: 82 Lock_time: 0 Rows_sent: 1 Rows_examined: 914386
select (mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)), {fn CONCAT({fn
CONCAT({fn CONCAT(cast(extract(hour from
`Time_Difference_Query`.`Created_Date`) as char(25)), ':')},
cast(extract(minute from `Time_Difference_Query`.`Created_Date`) as
char(25)))}, ':')}, `Time_Difference_Query`.`Created_Date`,
`Query1`.`Appointment_Provider_Name` from (select distinct
`EMR_ENCOUNTER`.`encType` as Encounter_Type , case when
`EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end as
Chart_Lock_Status , `EMR_ENCOUNTER`.`notesDoneTime` as Notes_Done_Time ,
`EMR_ENCOUNTER`.`dateOut` as Notes_Done_Date , `EMR_ENCOUNTER`.`timeIn` as
Appointments_Checked_In , `EMR_ENCOUNTER`.`timeOut` as
Appointments_Checked_Out , `EMR_ENCOUNTER`.`depTime` as
Appointments_Departure_Time , `EMR_ENCOUNTER`.`arrivedTime` as
Appointments_Arrived_Time , `EMR_ENCOUNTER`.`endTime` as
Appointment_End_Time , `EMR_ENCOUNTER`.`startTime` as
Appointment_Start_Time , `EMR_ENCOUNTER`.`date` as Appointment_Date ,
`EMR_ENCOUNTER`.`encounterID` as Encounter_ID , `EDI_FACILITIES`.`Name` as
Facility_Name , `APPOINTMENT_PROVIDER`.`uid` as Appointment_Provider_ID ,
{fn CONCAT({fn CONCAT({fn CONCAT({fn CONCAT(`APPOINTMENT_PROVIDER`.`ulname`, ',
')}, `APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
`APPOINTMENT_PROVIDER`.`uminitial`)} as Appointment_Provider_Name from
(`enc` `EMR_ENCOUNTER` LEFT OUTER JOIN `edi_facilities` `EDI_FACILITIES` on
`EMR_ENCOUNTER`.`facilityId` = `EDI_FACILITIES`.`Id`) LEFT OUTER JOIN
(`doctors` `APPOINTMENT_DOCTOR` INNER JOIN `users` `APPOINTMENT_PROVIDER` on
`APPOINTMENT_DOCTOR`.`doctorID` = `APPOINTMENT_PROVIDER`.`uid`) on
`EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID` where
`EMR_ENCOUNTER`.`encType` = 2 and `EMR_ENCOUNTER`.`date` between
cast('2011-01-01' as date) and cast('2013-05-07' as date) and
`EMR_ENCOUNTER`.`patientID` 8663 and `EMR_ENCOUNTER`.`VisitType` 'PTDASH'
and `EMR_ENCOUNTER`.`deleteFlag` = 0 and `APPOINTMENT_PROVIDER`.`UserType` = 1
and `APPOINTMENT_PROVIDER`.`delFlag` = 0 and `EDI_FACILITIES`.`DeleteFlag` = 0)
`Query1` LEFT OUTER JOIN (select distinct `Addressed_Query`.`moddate` as
Locked_Date , `Created_Query`.`moddate` as Created_Date ,
`Created_Query`.`encounterid` as encounterid , `Created_Query`.`reason` as
reason , `Created_Query`.`Patient_Name` as Patient_Name from (select
distinct `SQL1`.`moddate` as moddate , `SQL1`.`encounterid` as encounterid
, `SQL1`.`actionflag` as actionflag , `SQL1`.`ufname` as ufname ,
`SQL1`.`ulname` as ulname , `SQL1`.`reason` as reason , {fn CONCAT({fn
CONCAT(`SQL1`.`ulname`, ', ')}, `SQL1`.`ufname`)} as Patient_Name from
(select users.ufname,users.ulname,cast(reason as char(30)) as reason,
telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.moddate
from telenc_loghist
inner join enc on
enc.encounterid=telenc_loghist.encounterid
inner join users on users.uid=enc.patientid where
actionflag in(0) and enc.date between '2011-01-01' and '2013-05-07') `SQL1`)
`Created_Query` LEFT OUTER JOIN (select distinct `Q2`.`moddate` as moddate ,
`Q2`.`encounterid` as encounterid , `Q2`.`actionflag` as actionflag from
(select
telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.moddate
from telenc_loghist
inner join enc on
enc.encounterid=telenc_loghist.encounterid where actionflag in(4) and enc.date
between '2011-01-01' and '2013-05-07') `Q2`) `Addressed_Query` on
`Created_Query`.`encounterid` = `Addressed_Query`.`encounterid` where NOT
`Addressed_Query`.`moddate` is null) `Time_Difference_Query` on
`Query1`.`Encounter_ID` = `Time_Difference_Query`.`encounterid` where
`Query1
RE: Slow Response -- What Does This Sound Like to You?
`.`Facility_Name` in ('Fremont Family Care')
and `Query1`.`Appointment_Date` between cast(cast('2011-01-01' as date)
as date)
and cast(cast('2013-05-07' as date)
as date)
and `Query1`.`Appointment_Provider_ID` = 60922;
---
The big problem is
FROM ( SELECT ... ) JOIN ( SELECT ... ) ON ...
Neither of those subqueries has an index, so there will be table scans. The
solution is to CREATE TEMPORARY TABLE ... SELECT for each one, then add an
index.
You SELECT a bunch of rows as Query1, then filter?? Can't you move the
filtering into the subquery??
There is no need for either CAST in cast(cast('2013-05-07' as date) as date);
simply use '2013-05-07'.
What does the {} syntax do??
Contradictory:
where `Query1`.`Appointment_Provider_ID` in (9118, 9119, 60922, 9116,
47495)
and `Query1`.`Appointment_Provider_ID` = 60922;
The IN filter does nothing useful.
I think those changes will make the query run _much_ faster. If not, provide
the SHOW CREATE TABLE for the tables being used here, plus EXPLAIN SELECT.
-Original Message-
From: Robinson, Eric [mailto:eric.robin...@psmnv.com]
Sent: Friday, May 10, 2013 11:36 AM
To: Rick James; Bruce Ferrell; mysql@lists.mysql.com
Subject: RE: [Suspected Spam][Characteristics] RE: Slow Response --
What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections to be
blocked. Solution: switch to InnoDB. Caution: There are a few
caveats when switching; see
https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
2. As mentioned by Shawn, the Query Cache can be more trouble than it
is worth. However 90 seconds cannot be blamed on the QC. Still,
shrink it or turn it off:
* If frequently writing to tables, turn it off (type=OFF _and_
size=0)
* If less frequently, then decide which queries will benefit, add
SQL_CACHE to them, set type=DEMAND and size=50M (no larger).
3. Meanwhile, try to make that long query more efficient.
Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE
STATUS, and EXPLAIN ?
Thanks for the feedback, Rick.
There are 1200+ tables in the database, so I don't think you want a
SHOW CREATE TABLE, SHOW TABLE STATUS, EXPLAIN for all of them. :-) The
query in question is always some variation of the following. From
looking at this, which table(s) would you like to see this information
for?
# Time: 130507 18:14:26
# User@Host: site150_DbUser[site150_DbUser] @ cognos08.mycharts.md
[192.168.10.85] # Query_time: 82 Lock_time: 0 Rows_sent: 1
Rows_examined: 914386 select
(mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)), {fn CONCAT({fn
CONCAT({fn CONCAT(cast(extract(hour from
`Time_Difference_Query`.`Created_Date`) as char(25)), ':')},
cast(extract(minute from `Time_Difference_Query`.`Created_Date`) as
char(25)))}, ':')}, `Time_Difference_Query`.`Created_Date`,
`Query1`.`Appointment_Provider_Name` from (select distinct
`EMR_ENCOUNTER`.`encType` as Encounter_Type , case when
`EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end as
Chart_Lock_Status , `EMR_ENCOUNTER`.`notesDoneTime` as
Notes_Done_Time , `EMR_ENCOUNTER`.`dateOut` as Notes_Done_Date ,
`EMR_ENCOUNTER`.`timeIn` as Appointments_Checked_In ,
`EMR_ENCOUNTER`.`timeOut` as Appointments_Checked_Out ,
`EMR_ENCOUNTER`.`depTime` as Appointments_Departure_Time ,
`EMR_ENCOUNTER`.`arrivedTime` as Appointments_Arrived_Time ,
`EMR_ENCOUNTER`.`endTime` as Appointment_End_Time ,
`EMR_ENCOUNTER`.`startTime` as Appointment_Start_Time ,
`EMR_ENCOUNTER`.`date` as Appointment_Date ,
`EMR_ENCOUNTER`.`encounterID` as Encounter_ID ,
`EDI_FACILITIES`.`Name` as Facility_Name ,
`APPOINTMENT_PROVIDER`.`uid` as Appointment_Provider_ID , {fn
CONCAT({fn CONCAT({fn CONCAT({fn
CONCAT(`APPOINTMENT_PROVIDER`.`ulname`, ', ')},
`APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
`APPOINTMENT_PROVIDER`.`uminitial`)} as Appointment_Provider_Name
from (`enc` `EMR_ENCOUNTER` LEFT OUTER JOIN `edi_facilities`
`EDI_FACILITIES` on `EMR_ENCOUNTER`.`facilityId` =
`EDI_FACILITIES`.`Id`) LEFT OUTER JOIN (`doctors` `APPOINTMENT_DOCTOR`
INNER JOIN `users` `APPOINTMENT_PROVIDER` on
`APPOINTMENT_DOCTOR`.`doctorID` = `APPOINTMENT_PROVIDER`.`uid`) on
`EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID` where
`EMR_ENCOUNTER`.`encType` = 2 and `EMR_ENCOUNTER`.`date` between
cast('2011-01-01' as date) and cast('2013-05-07' as date) and
`EMR_ENCOUNTER`.`patientID` 8663 and `EMR_ENCOUNTER`.`VisitType`
'PTDASH' and `EMR_ENCOUNTER`.`deleteFlag` = 0 and
`APPOINTMENT_PROVIDER`.`UserType` = 1 and
`APPOINTMENT_PROVIDER`.`delFlag` = 0 and `EDI_FACILITIES`.`DeleteFlag`
= 0) `Query1` LEFT OUTER JOIN (select distinct
`Addressed_Query`.`moddate` as Locked_Date ,
`Created_Query`.`moddate` as Created_Date ,
`Created_Query`.`encounterid` as encounterid ,
`Created_Query`.`reason` as reason
RE: Slow Response -- What Does This Sound Like to You?
MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM -- Eric Robinson Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
-Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for Rick James,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
I delt with a similar situation where users complained the system would freeze up for 30-60 seconds at random intervals. After days of looking at queries, logs, error logs, etc.. We were no closer to finding a solution. We do have a service that runs every 15 minutes to cache some data in our system, in one app it creates some temporary tables. In this app the tables were not being created as memory tables. Since we also use connection pooling, the temporary tables created evey 15 minutes were not dropped when the task completed. When the connection was finally closed there were a lot of temporary tables to drop and the MySQL server would hang while this process was completed. Changing to memory tables solved the problem. Might not be your issue but it reminded me of this. On Thursday, May 9, 2013, Robinson, Eric wrote: We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM -- Eric Robinson Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com javascript:;. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Slow Response -- What Does This Sound Like to You?
Am 09.05.2013 22:58, schrieb Robinson, Eric: Q: What conditions could cause single query to lock up a database for a while for all users From http://docs.oracle.com/cd/E17952_01/refman-5.5-en/table-locking.html : A SELECT statement that takes a long time to run prevents other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session is waiting to get exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions. You might try using low_priority_updates to mitigate this. Regards, -- Denis Jedig syneticon networks gmbh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
-Original Message- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Thursday, May 09, 2013 3:50 PM To: Robinson, Eric Cc: Rick James; mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. I have not verified this, but it should be easy to find out. Hopefully that is not the case as it is a canned application and we don't have access to the code. --Eric Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for Wm Mussatto,Rick James,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
Hello Eric, On 5/9/2013 7:13 PM, Robinson, Eric wrote: -Original Message- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Thursday, May 09, 2013 3:50 PM To: Robinson, Eric Cc: Rick James; mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. I have not verified this, but it should be easy to find out. Hopefully that is not the case as it is a canned application and we don't have access to the code. --Eric Another option to keep in mind is the effect of a very large Query Cache. Each change to a table must invalidate every query (and their results) that derived from that table. For large caches, that can bring the server to a cold halt until the purge complete. Try disabling it entirely and see how that affects performance or make it much smaller. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections to be blocked. Solution: switch to InnoDB. Caution: There are a few caveats when switching; see https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/ 2. As mentioned by Shawn, the Query Cache can be more trouble than it is worth. However 90 seconds cannot be blamed on the QC. Still, shrink it or turn it off: * If frequently writing to tables, turn it off (type=OFF _and_ size=0) * If less frequently, then decide which queries will benefit, add SQL_CACHE to them, set type=DEMAND and size=50M (no larger). 3. Meanwhile, try to make that long query more efficient. Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN ? -Original Message- From: Bruce Ferrell [mailto:bferr...@baywinds.org] Sent: Thursday, May 09, 2013 6:05 PM To: mysql@lists.mysql.com Subject: Re: Slow Response -- What Does This Sound Like to You? On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
Hi everybody, I think we need to focus on three things:- A) temp tables created on disk B) table cache size C) buffer sizes If you find the number of temp tables created on disk is very large, please increase the temp_table_size. Enable the slow query log And check if sort buffer size and join buffer size needss to be increased if multiple joins are used. Also check whether the tables used in the slow queries do have index build on them or not. This heavily impacts the performance. If not create index on frequently used tables. Please try the above and let us know if resolved. Regards Vikas shukla -Original Message- From: Rick James rja...@yahoo-inc.com Sent: 10-05-2013 07:24 To: Bruce Ferrell bferr...@baywinds.org; mysql@lists.mysql.com mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? 1. MyISAM locks _tables_. That can cause other connections to be blocked. Solution: switch to InnoDB. Caution: There are a few caveats when switching; see https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/ 2. As mentioned by Shawn, the Query Cache can be more trouble than it is worth. However 90 seconds cannot be blamed on the QC. Still, shrink it or turn it off: * If frequently writing to tables, turn it off (type=OFF _and_ size=0) * If less frequently, then decide which queries will benefit, add SQL_CACHE to them, set type=DEMAND and size=50M (no larger). 3. Meanwhile, try to make that long query more efficient. Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN ? -Original Message- From: Bruce Ferrell [mailto:bferr...@baywinds.org] Sent: Thursday, May 09, 2013 6:05 PM To: mysql@lists.mysql.com Subject: Re: Slow Response -- What Does This Sound Like to You? On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Please do not open the link that was sent to you from my email address
Dear Friends, There has been some problems with my mail box and some spam emails have been sent to you from my email. Please do not open the link that was sent to you from my email address!! Sorry for the problem caused. Best regards, Javad Bakhshi, Computer Science M.Sc Department of IT, Uppsala University
In case you all missed it.
mysql select date_format(now(),'%m-%d%-%y %h:%i:%s') AS time; +---+ | time | +---+ | 11-11-11 11:11:11 | +---+ 1 row in set (0.00 sec)
Re: In case you all missed it.
On 11/11/2011 16:29, Curtis Maurand wrote: mysql select date_format(now(),'%m-%d%-%y %h:%i:%s') AS time; +---+ | time | +---+ | 11-11-11 11:11:11 | +---+ 1 row in set (0.00 sec) Actually, it should be select date_format(now(),'%y-%m%-%d %h:%i:%s') :-) Mark -- Sent from my Babbage Difference Engine http://mark.goodge.co.uk http://www.ratemysupermarket.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: In case you all missed it.
On Fri, 2011-11-11 at 16:38 +, Mark Goodge wrote: On 11/11/2011 16:29, Curtis Maurand wrote: mysql select date_format(now(),'%m-%d%-%y %h:%i:%s') AS time; +---+ | time | +---+ | 11-11-11 11:11:11 | +---+ 1 row in set (0.00 sec) Actually, it should be select date_format(now(),'%y-%m%-%d %h:%i:%s') :-) Mark -- Sent from my Babbage Difference Engine http://mark.goodge.co.uk http://www.ratemysupermarket.com *Actually*, it wouldn't make any difference what-so-ever which way round the tokens are!!! (for this case anyway) - Registered Office: 15 Stukeley Street, London WC2B 5LT, England. Registered in England number 1421223 This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. Please note that the information provided in this e-mail is in any case not legally binding; all committing statements require legally binding signatures. http://www.inflightproductions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Got It; Thank You; Re: Mysql Bug 04/01/11
Got It; Thank You, Thank You, Thank You On 4/1/2011 11:28 PM, Claudio Nanni wrote: Hi Thomas, Did you run the post install script? http://kae.li/iiikj Claudio On Apr 2, 2011 2:20 AM, Thomas Dineen tdin...@ix.netcom.com mailto:tdin...@ix.netcom.com wrote:
Re: is your lack of a degree holding you back?
That has got to be one of the most surreal conversations I've seen on a mailing list! Reminds me of the time I got tired of some boring lout on a Usenet group, and so I wrote up a markov-3 bot that would automatically respond to his postings, and then he got into a heated debate with himself... Reality is that which, when you stop believing in it, doesn't go away. -- Philip K. Dick Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
is your lack of a degree holding you back?
Dear friend, friend (instantdegree...@gmail.com) has sent you the following link at USAFlorist.com: http://www.usaflorist.com/products/1-800-flowers_fun__flirty.htm?refcode=1A2 Your Friend's Message to you: Do you have the knowledge and the experience in your field but lack the qualifications? Are you getting turned down time and time again for the job of your dreams because you just don't have the right letters after your name? Get the prestige that you deserve and move ahead in your career today! Bachelors, Masters, MBAs, and PhDs available in your field! No examinations, classes, or textbooks if you qualify! Contact us to register and receive your qualifications in as little as two weeks! 24 hours a day 7 days a week! Confidentiality assured! Contact us at: instantdegr...@linuxmail.org Just leave your NAME CONTACT INFO in the email. Our staff will get back to you in the next few days. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: is your lack of a degree holding you back?
Simon Thanks for your note. It sounds like you're having an interesting time. We're having a lot of fun with NimbusDB. It is one of those revolutionary architectural re-thinks that changes an industry. No-one else has worked out how to build an elastically scalable JDBC/SQL/transactional database. We're not talking about it openly yet, but sign up for the mailing list athttp://www.nimbusdb.com if you want more tech detail. As relates to your request, give me a call. I think the timing will work. Regards Barry On 2/12/11 4:46 AM, USAFlorist.com wrote: Dear friend, friend (instantdegree...@gmail.com) has sent you the following link at USAFlorist.com: http://www.usaflorist.com/products/1-800-flowers_fun__flirty.htm?refcode=1A2 Your Friend's Message to you: Do you have the knowledge and the experience in your field but lack the qualifications? Are you getting turned down time and time again for the job of your dreams because you just don't have the right letters after your name? Get the prestige that you deserve and move ahead in your career today! Bachelors, Masters, MBAs, and PhDs available in your field! No examinations, classes, or textbooks if you qualify! Contact us to register and receive your qualifications in as little as two weeks! 24 hours a day 7 days a week! Confidentiality assured! Contact us at: instantdegr...@linuxmail.org Just leave your NAME CONTACT INFO in the email. Our staff will get back to you in the next few days.
Re: is your lack of a degree holding you back?
Apologies - pls ignore my email below. On 2/12/11 1:01 PM, Barry Morris wrote: Simon Thanks for your note. It sounds like you're having an interesting time. We're having a lot of fun with NimbusDB. It is one of those revolutionary architectural re-thinks that changes an industry. No-one else has worked out how to build an elastically scalable JDBC/SQL/transactional database. We're not talking about it openly yet, but sign up for the mailing list athttp://www.nimbusdb.com if you want more tech detail. As relates to your request, give me a call. I think the timing will work. Regards Barry On 2/12/11 4:46 AM, USAFlorist.com wrote: Dear friend, friend (instantdegree...@gmail.com) has sent you the following link at USAFlorist.com: http://www.usaflorist.com/products/1-800-flowers_fun__flirty.htm?refcode=1A2 Your Friend's Message to you: Do you have the knowledge and the experience in your field but lack the qualifications? Are you getting turned down time and time again for the job of your dreams because you just don't have the right letters after your name? Get the prestige that you deserve and move ahead in your career today! Bachelors, Masters, MBAs, and PhDs available in your field! No examinations, classes, or textbooks if you qualify! Contact us to register and receive your qualifications in as little as two weeks! 24 hours a day 7 days a week! Confidentiality assured! Contact us at: instantdegr...@linuxmail.org Just leave your NAME CONTACT INFO in the email. Our staff will get back to you in the next few days.
Light a candle for 26/11 and Show you remember
Hi mysql@lists.mysql.com , I just light a candle for 26/11. It is time to show that we have neither forgiven nor forgotten 26/11. It is time to remember those who paid with their lives for the fanaticism of a few, to salute those who gave up their lives trying to shield others, and to honor those who survived the ordeal of terror. It is time to light a candle, to show that as we move on, we cherish their memories, we value their sacrifice and we hail their courage. Please Light a Candle Now at: http://www.indiajaiho.com/LightACandle.htm Warm Regards, Sharath -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Thanks again :-) Nunzio From: Joerg Bruehe joerg.bru...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com; mysQL General List mysql@lists.mysql.com Sent: Fri, July 30, 2010 1:31:54 PM Subject: Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?) Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Nunzio Daveri, Joerg Bruehe gave you a lot of good tips to try and speed things up. A few hundred queries per second seem to be a relatively small number to cause the server to crawl. I don't have the rest of your thread, but can you publish some of the slow queries (see Slow Query Log) and the table structure? Mike At 01:31 PM 7/30/2010, you wrote: Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can't execute the given command because you have active locked tables or an active transaction
Hi All,I am getting Can't execute the given command because you have active locked tables or an active transaction error when I am trying to truncate table. I am unable to understand the error as when I am using the mysql query browser then the same command is working fine but when I am doing it through NetBeans then I am getting this error. Well that is what is very strange and I am unable to solve this problem.If anyone could just help me with this. Thanks in advance.--Regards, Manasi Save
Re: Can't execute the given command because you have active locked tables or an active transaction
Hi, I am getting Can't execute the given command because you have active locked tables or an active transaction error when I am trying to truncate table. I am unable to understand the error as when I am using the mysql query browser then the same command is working fine but when I am doing it through NetBeans then I am getting this error. Well that is what is very strange and I am unable to solve this problem. Try enabling MySQL querie log: log=/var/log/somelog.log and execute the command from netbeans. Look at the log and execute again from query browser. Now, look if there is any difference on the log (perhaps netbeans execute some other queries) ;) -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
This only works for MyISAM :-) However, there's another solution where you don't need to shut down, and that works for any engine afaik: rename table oldschema.table to newschema.table; I agree that it's a silly thing to not have, but I can't say that I've encountered a whole lot of instances where I needed it, either. On Fri, Dec 11, 2009 at 7:04 AM, Ken D'Ambrosio k...@jots.org wrote: Uhhh... wow. Unless I'm very, very, very mistaken, I think you're missing something pretty obvious: I believe you can simply a) shut down the database b) mv the directory to a different directory name. *DONE* Your database now has a different name. Boy, that 30 seconds of hard labor was sure faster than waiting a week for SQL dumps. Granted, I can't swear that this is Officially Sanctioned And Approved(tm), but I've done it many times, myself (and, indeed, just verified it under 5.1 to be sure it still worked). Since you are talking such a significant volume of data, I would suggest either testing, or hearing from someone more knowledgeable than I, but I think this problem is substantially smaller than you've let yourself believe. -Ken On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote: How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onst at us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_det ai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viabilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
On Fri, December 11, 2009 7:38 am, Johan De Meersman wrote: This only works for MyISAM :-) Good to know -- thanks! However, there's another solution where you don't need to shut down, and that works for any engine afaik: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? I agree that it's a silly thing to not have, but I can't say that I've encountered a whole lot of instances where I needed it, either. Agreed. Thanks much! -Ken On Fri, Dec 11, 2009 at 7:04 AM, Ken D'Ambrosio k...@jots.org wrote: Uhhh... wow. Unless I'm very, very, very mistaken, I think you're missing something pretty obvious: I believe you can simply a) shut down the database b) mv the directory to a different directory name. *DONE* Your database now has a different name. Boy, that 30 seconds of hard labor was sure faster than waiting a week for SQL dumps. Granted, I can't swear that this is Officially Sanctioned And Approved(tm), but I've done it many times, myself (and, indeed, just verified it under 5.1 to be sure it still worked). Since you are talking such a significant volume of data, I would suggest either testing, or hearing from someone more knowledgeable than I, but I think this problem is substantially smaller than you've let yourself believe. -Ken On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote: How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=on; st at us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os _det ai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viab ilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-)
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Can you use that syntax if the databases are on different file systems? If you can, and the original table is big, the command would take a while as it moved data from one file system to another. On Fri, Dec 11, 2009 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
According to MySQL docs, it should still work atomically. Granted, I have only used this particular trick when they are on the same filesystem. Copying across filesystems, I imagine it should still be atomic, but your system may be locked for awhile. Obviously, a dedicated RENAME DATABASE command would have the same limitations. - michael dykman On Fri, Dec 11, 2009 at 11:35 AM, Jim Lyons jlyons4...@gmail.com wrote: Can you use that syntax if the databases are on different file systems? If you can, and the original table is big, the command would take a while as it moved data from one file system to another. On Fri, Dec 11, 2009 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- 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=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
On Fri, Dec 11, 2009 at 10:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? Yes. If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. Not really, this is by design afaik. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) Don't know, but given that it works with InnoDB, you should be able to easily test on a small dataset by monitoring the size of a 1M autoextend tablespace :-)
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
if you have myisam alone tables you can rename the folder of the database. That can work like rename database. If you have innodb table you have to move one by one table because details of those tables will be stored in innodb shared table space. Moving folder cannot work. Thanks, Saravanan --- On Fri, 12/11/09, Michael Dykman mdyk...@gmail.com wrote: From: Michael Dykman mdyk...@gmail.com Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? To: MySql mysql@lists.mysql.com Date: Friday, December 11, 2009, 10:54 PM No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=suzuki_b...@yahoo.com
RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Don't forget triggers, stored routines, views, database/table specific user permissions, and replication/binlog options! Regards, Gavin Towey -Original Message- From: Saravanan [mailto:suzuki_b...@yahoo.com] Sent: Friday, December 11, 2009 2:02 PM To: MySql; Michael Dykman Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? if you have myisam alone tables you can rename the folder of the database. That can work like rename database. If you have innodb table you have to move one by one table because details of those tables will be stored in innodb shared table space. Moving folder cannot work. Thanks, Saravanan --- On Fri, 12/11/09, Michael Dykman mdyk...@gmail.com wrote: From: Michael Dykman mdyk...@gmail.com Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? To: MySql mysql@lists.mysql.com Date: Friday, December 11, 2009, 10:54 PM No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=suzuki_b...@yahoo.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.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: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
In our case, we purposely avoid using any of those features. Just straight up INNODB tables. Permissions would be an issue, but in my case, I have a new dump of a database that I want to 'swap' with the existing one. A simple rename old, rename new to old would have solved it. Hence this thread. :) Therefore permissions should be fine as they go by DB name AFAIK and not some pointer. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, December 11, 2009 2:18 PM To: Saravanan; MySql; Michael Dykman Subject: RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? Don't forget triggers, stored routines, views, database/table specific user permissions, and replication/binlog options! Regards, Gavin Towey -Original Message- From: Saravanan [mailto:suzuki_b...@yahoo.com] Sent: Friday, December 11, 2009 2:02 PM To: MySql; Michael Dykman Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? if you have myisam alone tables you can rename the folder of the database. That can work like rename database. If you have innodb table you have to move one by one table because details of those tables will be stored in innodb shared table space. Moving folder cannot work. Thanks, Saravanan --- On Fri, 12/11/09, Michael Dykman mdyk...@gmail.com wrote: From: Michael Dykman mdyk...@gmail.com Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? To: MySql mysql@lists.mysql.com Date: Friday, December 11, 2009, 10:54 PM No, not a loophole. Just a plain-old management feature.. there is nothing particularly hacky about it.. this is not trying to leverage undocumented features: this has been a published part of the API for at least a couple of years. On the same file system, yes it should be pretty damned fast. Depending on how your data is stored, it might now be 'quite' as simple as a unix 'mv' command.. if this is a production system, I would recommend you do a dry run with a replicant/slave. No amount of theorizing will tell as much as the experiment. - michael dykman On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent dae...@daevid.com wrote: Will this work in 5.0? If I'm reading this right, it seems like this is some kind of trick or loophole then right? If it works and solves my dilemna, I'm fine with that, but I'm just curious. How fast is this? I mean, if I have an 80GB database, is it like a real unix 'mv' command where it simply changing pointers or is it a full on copy/rm? (Assume same filesystem/directory) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, December 11, 2009 6:08 AM To: MySql Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE? If you want to move the database atomically, a RENAME TABLE statement may have multiple clauses. RENAME TABLE olddb.foo to newdb.foo, olddb.bar to newdb.bar; Here, I hot-swap a new lookup table 'active.geo' into a live system confident that, at any given point, some version of this table always exists: RENAME TABLE active.geo to archive.geo, standby.geo to active geo; - michael dykman On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio k...@jots.org wrote: rename table oldschema.table to newschema.table; Just to be 100% clear -- I assume you have to first create the destination database, and then do this for all the tables in the source database? Yep. Easily scriptable, though :-) -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=suzuki_b...@yahoo.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender
Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onstat us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_detai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viability= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
Uhhh... wow. Unless I'm very, very, very mistaken, I think you're missing something pretty obvious: I believe you can simply a) shut down the database b) mv the directory to a different directory name. *DONE* Your database now has a different name. Boy, that 30 seconds of hard labor was sure faster than waiting a week for SQL dumps. Granted, I can't swear that this is Officially Sanctioned And Approved(tm), but I've done it many times, myself (and, indeed, just verified it under 5.1 to be sure it still worked). Since you are talking such a significant volume of data, I would suggest either testing, or hearing from someone more knowledgeable than I, but I think this problem is substantially smaller than you've let yourself believe. -Ken On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote: How can it possibly be that mySQL doesn't allow you to rename a database? I can't fathom how this can be a difficult task at all to do. Aren't mySQL databases stored in a directory of the DB name? And for INNODB, can't you just find the spot in the ibdata file and alter whatever needs to be changed? This is absolutely absurd. Not even 5.1 has this most basic of features. We have nearly a billion rows. Exporting to a .sql file and importing again can take nearly a week to do (3 days each way and that doesn't even begin to touch on the fact the server would be down)! WTF!? We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Even the manual for 5.1 says this can lose data: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23...However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. Seriously? Please explain why a simple rename of a database is such a daunting task to mySQL/Sun that all their brilliant minds can't figure this one out? Why isn't there even a bug report for this? http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onst at us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_det ai ls=bug_age=0tags=similar=target=defect_class=allworkaround_viabilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Special for you!
http://cfi.iflywestwind.com/nEACWal3iJ.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Access denied; you need the RELOAD privilege for this operation
Hi everyone -- I'm pretty new to MySql, but not many years ago I was an ISAM guy so I understand the issues with indexes and on-the-fly inserts. I've seen many questions around this error message, Access denied; you need the RELOAD privilege for this operation. But I'm not grokking the answers. Is it true that I cannot get all the privs I need on a database that I have created? If so, then what's a reasonable way for MySql users to get around this restriction? In my particular case, cPanel tells me MySQL version 5.0.81-community. I have a table usrs in my ISAM DB into which I want to insert a row. I execute this C-connector code before the insert: err = mysql_query( pmysql, lock table usrs write; ); err = mysql_query( pmysql, flush table usrs; ); The error return from flush table is Access denied; you need the RELOAD privilege for this operation After the insert (which completes without error) I execute the flush table call again prior to issuing unlock tables; The error return to the second flush table is likewise Access denied. I think I understand (from reading the net) that only root can grant RELOAD privs. So of course the many responses to this question everywhere often begin, It's so very simple! Just login as root and ... :-) Since I'm on a shared-host ISP, I need to get the admin to grant me RELOAD privs. But it seems that RELOAD priv is a MySql-wide priv, not a per-database or per-user priv. So no admin will go for that. The other alternative, I guess, is for me to ask the admin to execute a flush table on my table from time to time, every few days, say. Do I have the correct understanding of the situation? And how do people usually deal with this error? Thanks so much! I apologize for the newb-level question and really appreciate your help. -- Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Access denied; you need the RELOAD privilege for this operation
In the last episode (Sep 20), Pete Wilson said: I'm pretty new to MySql, but not many years ago I was an ISAM guy so I understand the issues with indexes and on-the-fly inserts. I've seen many questions around this error message, Access denied; you need the RELOAD privilege for this operation. But I'm not grokking the answers. Is it true that I cannot get all the privs I need on a database that I have created? If so, then what's a reasonable way for MySql users to get around this restriction? In my particular case, cPanel tells me MySQL version 5.0.81-community. I have a table usrs in my ISAM DB into which I want to insert a row. I execute this C-connector code before the insert: err = mysql_query( pmysql, lock table usrs write; ); err = mysql_query( pmysql, flush table usrs; ); The error return from flush table is Access denied; you need the RELOAD privilege for this operation After the insert (which completes without error) I execute the flush table call again prior to issuing unlock tables; The error return to the second flush table is likewise Access denied. The only time a table flush is needed is when you are looking at the raw mysql data files and want to make sure there are no dirty blocks that haven't been written to disk yet (for hot backup purposes, for example), or if you are debugging a performance problem and want to make sure all caches are emptied. If you're just inserting rows, there's no need to flush anything. Also, there probably isn't a reason to explicitly lock the entire table, either. If you were previously using that to emulate atomic transactions on myisam tables, you can replace your lock/unlock table operations with a START TRANSACTION/COMMIT pair, because innodb supports transactions (myisam doesn't). That will let other users access the usrs table at the same time you're doing your insert. If you're just inserting a single row, you can even drop the transaction calls. A single insert is always atomic, no matter what table type you are using. Your ISAM experience might actually be a drawback here, if it's more at the operational level of old Berkely DB 1.x-style databases (where one has to manually maintain the index, there are no transactions, etc). SQL-based databases sit on top of the lower-level table engines and handle all that work for you. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How do you show ALL grants for a username?
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR ''@'pse01'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'%'; |
| SHOW GRANTS FOR 'madc'@'10.10.10.%';|
| SHOW GRANTS FOR 'madc'@'10.10.10.42'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
But how do I see all the grants that madc has? I would have expected the %
wildcard to work, but mysql uses it as a literal!?
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'%';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How do you remove a user from the grant table?!!
How the F do you remove a user from the grant table?!!
The mysql.com site is down too by the way...
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc';
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
'%'
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
'%'
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'localhost';
+--+
| Grants for m...@localhost|
+--+
| GRANT USAGE ON *.* TO 'madc'@'localhost' |
| GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'localhost' |
+--+
2 rows in set (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'localhost';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO
'madc'@'127.0.0.1' IDENTIFIED BY 'madc';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'127.0.0.1';
+---
--+
| Grants for m...@127.0.0.1
|
+---
--+
| GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' |
| GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'127.0.0.1'
|
+---
--+
2 rows in set (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES, GRANT OPTION FROM
'madc'@'localhost';
Query OK, 0 rows affected (0.01 sec)
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR ''@'pse05'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'127.0.0.1';
+---
--+
| Grants for m...@127.0.0.1
|
+---
--+
| GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' |
+---
--+
1 row in set (0.00 sec)
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES FROM 'madc'@'127.0.0.1';
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 'FROM 'madc'@'127.0.0.1'' at line 1
(r...@localhost) [(none)] REVOKE ALL PRIVILEGES ON 'madc'@'127.0.0.1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your
Re: How do you show ALL grants for a username?
Use information_schema!
select * from information_schema.user_privileges where grantee like
'madc'@%;
Should get you what you need.
John Daisley
Email: john.dais...@butterflysystems.co.uk
Mobile: +44 (0)7812 451238
MySQL Certified Database Administrator (CMDBA)
MySQL Certified Developer (CMDEV)
MySQL Certified Associate (CMA)
Comptia A+ Certified Professional IT Technician
---
Life's journey is not to arrive at the grave safely in a well preserved
body, but rather to slide in sideways, thoroughly used up, totally worn
out and screaming Wow! what a ride!
On Wed, 2009-07-22 at 12:58 -0700, Daevid Vincent wrote:
(r...@localhost) [(none)] SELECT CONCAT('SHOW GRANTS FOR \'', user
,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;
+-+
| mygrants|
+-+
| SHOW GRANTS FOR ''@'localhost'; |
| SHOW GRANTS FOR ''@'pse01'; |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'%'; |
| SHOW GRANTS FOR 'madc'@'10.10.10.%';|
| SHOW GRANTS FOR 'madc'@'10.10.10.42'; |
| SHOW GRANTS FOR 'madc'@'127.0.0.1'; |
| SHOW GRANTS FOR 'madc'@'localhost'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'slave'@'10.10.10.%'; |
| SHOW GRANTS FOR 'slave_user'@'%'; |
+-+
But how do I see all the grants that madc has? I would have expected the %
wildcard to work, but mysql uses it as a literal!?
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@'%';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc';
+---
-+
| Grants for m...@%
|
+---
-+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---
-+
(r...@localhost) [(none)] SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''
RE: How do you remove a user from the grant table?!!
*mysql create user 'test'@'localhost' identified by 'pass';* Query OK, 0 rows affected (0.00 sec) *mysql GRANT CREATE, DELETE ON *.* TO 'test'@'localhost';* Query OK, 0 rows affected (0.00 sec) *mysql select * from information_schema.user_privileges where grantee like 'test'@'localhost';* ++---++--+ | GRANTEE| TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | ++---++--+ | 'test'@'localhost' | NULL | DELETE | NO | | 'test'@'localhost' | NULL | CREATE | NO | ++---++--+ 2 rows in set (0.00 sec) *mysql REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test'@'localhost';* Query OK, 0 rows affected (0.00 sec) *mysql select * from information_schema.user_privileges where grantee like 'test'@'localhost';* ++---++--+ | GRANTEE| TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | ++---++--+ | 'test'@'localhost' | NULL | USAGE | NO | ++---++--+ * http://dev.mysql.com/doc/refman/5.0/en/revoke.htmlREVOKEhttp://dev.mysql.com/doc/refman/5.0/en/revoke.htmlremoves privileges, but does not drop mysql.user table entries. To remove a user account entirely, use DROP USERhttp://dev.mysql.com/doc/refman/5.0/en/drop-user.html mysql drop user 'test'@'localhost; mysql select * from information_schema.user_privileges where grantee like 'test'@'localhost'; Empty set (0.00 sec) *
Re: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Hi all!
Mark wrote:
On my FreeBSD:
usr/include/errno.h:#define EAGAIN 35 /* Resource temporarily unavailable
*/
/usr/include/errno.h-#ifndef _POSIX_SOURCE
/usr/include/errno.h-#define EWOULDBLOCK EAGAIN /* Operation would block */
Ok, next step then:
The OP wrote the problem was when creating a new thread.
From some obscure sources (personal knowledge, reading, wild guess, ...)
we assume it might be a system call pthread_create() which is used for
this.
So we do a man pthread_create and get (quoted from my Linux machine):
| NAME
| pthread_create - thread creation
Looks good! Continuing reading that page, we arrive at (again, from Linux):
| ERRORS
|The pthread_create() function shall fail if:
|
|EAGAIN The system lacked the necessary resources to create
another thread, or the system-imposed
| limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|
I leave it to the OP (or other users of his system, FreeBSD 7.1) to
1) check the man page there for the exact description when
pthread_create() might cause error EAGAIN,
2) find out which resources that might be, or which system limit, and
how to check more, maybe even change the limits.
I still wouldn't discount the files resources limit; though obviously
I bow to Jörg's expertise on the matter. :)
Thanks :)
I never claimed it wouldn't be files, I just said that using the errno
value reported will help in the analysis (and tried to explain how to do
that).
But if somebody finds that the message
*1135: Can't create a new thread (errno 35); if you are not out of
available memory, you can consult the manual for a possible
OS-dependent bug*
really is caused by a file limit, I propose to report a bug about a
misleading error message.
Regards,
Jörg
--
Joerg Bruehe, MySQL Build Team,
joerg.bru...@sun.com
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
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=arch...@jab.org
RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Seems I was in error suggesting a file limit issue; which is why I wisely
left it to to, Jörg. :)
Ok, next step, then:
ERRORS
pthread_create() will fail if:
[EAGAIN] The system lacked the necessary resources to create
another thread, or the system-imposed limit on the
total number of threads in a process
[PTHREAD_THREADS_MAX] would be exceeded.
Which brought me to the likely solution:
http://www.krellis.org/unix-stuff/mysql-freebsd-threads.html
My system only needs 128 connections (as opposed to the 2000 of the OP),
so I guess that's why I never ran into this.
- Mark
-Original Message-
From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com]
Sent: woensdag 29 april 2009 8:14
To: Mark; mysql@lists.mysql.com
Subject: Re: 1135: Can't create a new thread (errno 35); if you are not out
of available memory, you can consult the manual for a possible OS-dependent
bug
Hi all!
Mark wrote:
On my FreeBSD:
usr/include/errno.h:#define EAGAIN 35 /* Resource temporarily unavailable
*/
/usr/include/errno.h-#ifndef _POSIX_SOURCE
/usr/include/errno.h-#define EWOULDBLOCK EAGAIN /* Operation would block
*/
Ok, next step then:
The OP wrote the problem was when creating a new thread.
From some obscure sources (personal knowledge, reading, wild guess, ...)
we assume it might be a system call pthread_create() which is used for
this.
So we do a man pthread_create and get (quoted from my Linux machine):
| NAME
| pthread_create - thread creation
Looks good! Continuing reading that page, we arrive at (again, from Linux):
| ERRORS
|The pthread_create() function shall fail if:
|
|EAGAIN The system lacked the necessary resources to create
another thread, or the system-imposed
| limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|
I leave it to the OP (or other users of his system, FreeBSD 7.1) to
1) check the man page there for the exact description when
pthread_create() might cause error EAGAIN,
2) find out which resources that might be, or which system limit, and
how to check more, maybe even change the limits.
I still wouldn't discount the files resources limit; though obviously
I bow to Jörg's expertise on the matter. :)
Thanks :)
I never claimed it wouldn't be files, I just said that using the errno
value reported will help in the analysis (and tried to explain how to do
that).
But if somebody finds that the message
*1135: Can't create a new thread (errno 35); if you are not out of
available memory, you can consult the manual for a possible
OS-dependent bug*
really is caused by a file limit, I propose to report a bug about a
misleading error message.
Regards,
Jörg
--
Joerg Bruehe, MySQL Build Team,
joerg.bru...@sun.com
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
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=ad...@asarian-host.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Hello guys and gurus I am keep getting this error after a while *1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug* Even though, I have 16GB memory and 32GB swap. But mysqlserver stops answering. Could you please help me to fix this problem? I am running FreeBSD on a Dell PowerEdge 2950 III having 2 x CPU 3,0 GHz Intel Xeon L5450 Quad-Core 2x6MB cache WITH 16 GB RAM Here is a snapshot from using top to have a overview of system resources... last pid: 27056; load averages: 1.05, 1.04, 1.01 up 3+16:22:44 09:01:53 3038 processes:2 running, 3036 sleeping CPU: 12.4% user, 0.0% nice, 0.1% system, 0.0% interrupt, 87.4% idle Mem: 6237M Active, 4005M Inact, 925M Wired, 40K Cache, 214M Buf, 4491M Free Swap: 32G Total, 32G Free PID USERNAMETHR PRI NICE SIZERES STATE C TIME WCPU COMMAND 3010 mysql 1500 510 1643M 822M ucond 2 0:01 100.00% mysqld 27030 sshUser 1 440 16304K 8316K CPU0 0 0:01 0.39% top 26838 sshUser 1 450 26448K 2464K select 5 0:22 0.20% sshd 853 root 1 440 114M 19060K select 0 0:32 0.00% httpd 790 postfix 1 40 4600K 2120K kqread 2 0:12 0.00% qmgr 23542 apache1 40 118M 46520K sbwait 3 0:09 0.00% httpd 23420 apache1 40 118M 42340K sbwait 7 0:08 0.00% httpd 23543 apache1 40 118M 51032K sbwait 2 0:08 0.00% httpd 22853 apache1 40 118M 48384K sbwait 4 0:07 0.00% httpd 23768 apache1 40 118M 35432K sbwait 0 0:07 0.00% httpd 23748 apache1 40 118M 36560K sbwait 2 0:07 0.00% httpd 22861 apache1 40 118M 48420K sbwait 2 0:07 0.00% httpd 23618 apache1 40 117M 45696K sbwait 2 0:06 0.00% httpd 23222 apache1 40 118M 46080K sbwait 3 0:06 0.00% httpd 23700 apache1 40 118M 39572K sbwait 0 0:06 0.00% httpd 23534 apache1 40 118M 43984K sbwait 0 0:06 0.00% httpd 23439 apache1 40 118M 42980K sbwait 4 0:06 0.00% httpd 23480 apache1 40 118M 42724K sbwait 0 0:05 0.00% httpd 782 root 1 40 4604K 1552K kqread 2 0:05 0.00% master 26843 sshUser 1 440 21560K 5824K select 1 0:05 0.00% sftp-server 23066 apache1 40 118M 42328K sbwait 3 0:05 0.00% httpd 23619 apache1 40 118M 48012K sbwait 3 0:05 0.00% httpd 23224 apache1 40 118M 46436K sbwait 3 0:05 0.00% httpd 23220 apache1 40 118M 50776K sbwait 3 0:05 0.00% httpd 23176 apache1 40 118M 44956K sbwait 5 0:04 0.00% httpd 23467 apache1 40 118M 41692K sbwait 0 0:04 0.00% httpd 23294 apache1 40 116M 45552K sbwait 0 0:04 0.00% httpd 22884 apache1 40 118M 48596K sbwait 0 0:04 0.00% httpd 23214 apache1 40 118M 48508K sbwait 4 0:04 0.00% httpd 23177 apache1 40 118M 44844K sbwait 6 0:04 0.00% httpd 23278 apache1 40 117M 44812K sbwait 5 0:04 0.00% httpd 23497 apache1 40 117M 41612K sbwait 0 0:04 0.00% httpd 23477 apache1 40 118M 42332K sbwait 2 0:04 0.00% httpd 23371 apache1 40 118M 42176K sbwait 4 0:04 0.00% httpd 23563 apache1 40 118M 45096K sbwait 2 0:04 0.00% httpd 629 root 1 440 5688K 1252K select 0 0:04 0.00% syslogd 23119 apache1 40 118M 42088K sbwait 0 0:03 0.00% httpd Any help will be appreciated -- Thanks! BR / vj
Re: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Hi folks then if I check with the process: I get following output: # ps ax | grep mysqld 797 con- I 0:00.00 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/localhost.server1.pid 835 con- S 8:17.81 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --pid-file=/var/db/mysql/localhost.server1.pid --port=3306 -- I am running FreeBSD, Apache, MySQL on a Dell PowerEdge 2950 III having 2 x CPU 3,0 GHz Intel Xeon L5450 Quad-Core 2x6MB cache WITH 16 GB RAM Below is the output from my my.cnf, may be that can help to fix this problem: -start # The following options will be passed to all MySQL clients [client] port= 3306 socket= /tmp/mysql.sock default-character-set=utf8 # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 1024M max_allowed_packet = 16M table_cache = 1024 sort_buffer_size = 3M read_buffer_size = 12M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M user=mysql set-variable=local-infile=0 init_connect='SET collation_connection = utf8_general_ci' init_connect='SET NAMES utf8' default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci bind-address=127.0.0.1 skip-innodb skip-name-resolve default-storage-engine = MyISAM wait_timeout = 60 log_slow_queries = /var/db/mysql/mysqld.slow.log long_query_time=3 log-queries-not-using-indexes connect_timeout=10 join_buffer=3M max_connections = 2000 query_cache_type = 1 query_cache_limit = 2M query_cache_size = 128M skip-name-resolve thread_cache_size = 8 thread_concurrency = 8 interactive_timeout=100 join_buffer_size=2M key_buffer_size=1024M max_connect_errors=1000 ft_min_word_len=2 ft_max_word_len=15 skip-networking log-bin=mysql-bin expire_logs_days=7 server-id= 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -end -- Thanks! BR / vj
RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
I happen to run the exact same MySQL version + OS version (although I'm doing a huge upgrade to 5.1.34 today), and I have not experienced any such issues. Granted, I only get ~60 queries/s, so your traffic is probably much higer. At any rate, the first place I'd look is for open files limit (and 'sysctl' it to higher if it's not sufficient). I'm always amazed how much open files MySQL keeps. The amount of files MySQL reserves, way I recall, is also directly related to max. connections; so you could lower that too, temporarily, to see if it makes the error go away. - Mark -Original Message- From: VeeJay [mailto:maan...@gmail.com] Sent: dinsdag 28 april 2009 11:27 To: Mark Cc: mysql@lists.mysql.com Subject: Re: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Hi Mark Yes, you are right. I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote: It would probably help if you told folks what MySQL version you are running. :) - Mark -Original Message- From: VeeJay [mailto:maan...@gmail.com] Sent: dinsdag 28 april 2009 10:22 To: mysql@lists.mysql.com; VeeJay Subject: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Hello guys and gurus I am keep getting this error after a while *1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maan...@gmail.com -- Thanks! BR / vj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Hi VeeJay, Mark, all, VeeJay wrote: Hi Mark Yes, you are right. I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote: It would probably help if you told folks what MySQL version you are running. :) - Mark -Original Message- From: VeeJay [mailto:maan...@gmail.com] Sent: dinsdag 28 april 2009 10:22 To: mysql@lists.mysql.com; VeeJay Subject: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Hello guys and gurus I am keep getting this error after a while *1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug* Why don't you believe the mesage? It clearly says possible OS-dependent bug, so the OS is where to look. Also, 35 is a pretty low number, so I expect it to be an errno as provided by the operating system. (Which agrees with the message text, that says errno 35.) I don't have FreeBSD available, on my Linux the code 35 means EDEADLK: /usr/include/asm-generic/errno.h:6:#define EDEADLK 35 /* Resource deadlock would occur */ For such searches, I have my tiny script FGIN: #! /bin/sh # # FGIN shell script to use fgrep on all /usr/include/h files # # $1, $2, ... options and arguments given to fgrep # # Simple example: #FGIN seteuid return the line(s) in system header files where # 'seteuid()' is defined or otherwise mentioned. # # 2004-09-13 Joerg Bruehe Initial published version find /usr/include -follow -name '*.h' -print | xargs fgrep -n $* As 35 my be a common term, I do FGIN 35 | fgrep errno I propose you do something similar on your machine. Granted, this still doesn't tell you what exactly happened, but it should help to narrow the possible causes. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer 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=arch...@jab.org
RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Jörg mgai...@martini ~ FGIN.sh 35 | fgrep errno find: File system loop detected; `/usr/include/gnome-xml/libxml' is part of the same file system loop as `/usr/include/gnome-xml'. /usr/include/apr-1/apr_errno.h:806:#define SOCEWOULDBLOCK (SOCBASEERR+3 5)/* Operation would block */ /usr/include/mingw/errno.h:60:/* 35 - Unknown Error */ Vielen Danke! Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Tue, 28 Apr 2009 12:26:47 +0200 From: joerg.bru...@sun.com Subject: Re: 1135: Can't create a new thread (errno 35); if you are not out ofavailable memory, you can consult the manual for a possible OS-dependent bug To: maan...@gmail.com CC: ad...@asarian-host.net; mysql@lists.mysql.com Hi VeeJay, Mark, all, VeeJay wrote: Hi Mark Yes, you are right. I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote: It would probably help if you told folks what MySQL version you are running. :) - Mark -Original Message- From: VeeJay [mailto:maan...@gmail.com] Sent: dinsdag 28 april 2009 10:22 To: mysql@lists.mysql.com; VeeJay Subject: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Hello guys and gurus I am keep getting this error after a while *1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug* Why don't you believe the mesage? It clearly says possible OS-dependent bug, so the OS is where to look. Also, 35 is a pretty low number, so I expect it to be an errno as provided by the operating system. (Which agrees with the message text, that says errno 35.) I don't have FreeBSD available, on my Linux the code 35 means EDEADLK: /usr/include/asm-generic/errno.h:6:#define EDEADLK 35 /* Resource deadlock would occur */ For such searches, I have my tiny script FGIN: #! /bin/sh # # FGIN shell script to use fgrep on all /usr/include/h files # # $1, $2, ... options and arguments given to fgrep # # Simple example: #FGIN seteuid return the line(s) in system header files where # 'seteuid()' is defined or otherwise mentioned. # # 2004-09-13 Joerg Bruehe Initial published version find /usr/include -follow -name '*.h' -print | xargs fgrep -n $* As 35 my be a common term, I do FGIN 35 | fgrep errno I propose you do something similar on your machine. Granted, this still doesn't tell you what exactly happened, but it should help to narrow the possible causes. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer 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=mgai...@hotmail.com _ Rediscover Hotmail®: Get e-mail storage that grows with you. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage2_042009
Re: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
Hi Mark Yes, you are right. I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote: It would probably help if you told folks what MySQL version you are running. :) - Mark -Original Message- From: VeeJay [mailto:maan...@gmail.com] Sent: dinsdag 28 april 2009 10:22 To: mysql@lists.mysql.com; VeeJay Subject: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Hello guys and gurus I am keep getting this error after a while *1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maan...@gmail.com -- Thanks! BR / vj
RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
On my FreeBSD: usr/include/errno.h:#define EAGAIN 35 /* Resource temporarily unavailable */ /usr/include/errno.h-#ifndef _POSIX_SOURCE /usr/include/errno.h-#define EWOULDBLOCK EAGAIN /* Operation would block */ I still wouldn't discount the files resources limit; though obviously I bow to Jörg's expertise on the matter. :) - Mark -Original Message- From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: dinsdag 28 april 2009 14:44 To: Joerg Bruehe; maan...@gmail.com Cc: ad...@asarian-host.net; mysql@lists.mysql.com Subject: RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Jörg mgai...@martini ~ FGIN.sh 35 | fgrep errno find: File system loop detected; `/usr/include/gnome-xml/libxml' is part of the same file system loop as `/usr/include/gnome-xml'. /usr/include/apr-1/apr_errno.h:806:#define SOCEWOULDBLOCK (SOCBASEERR+3 5)/* Operation would block */ /usr/include/mingw/errno.h:60:/* 35 - Unknown Error */ Vielen Danke! Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Tue, 28 Apr 2009 12:26:47 +0200 From: joerg.bru...@sun.com Subject: Re: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug To: maan...@gmail.com CC: ad...@asarian-host.net; mysql@lists.mysql.com Hi VeeJay, Mark, all, VeeJay wrote: Hi Mark Yes, you are right. I should have provided complete information in order to get help... I am running DB: Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1 OS: FreeBSD 7.1 On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote: It would probably help if you told folks what MySQL version you are running. :) - Mark -Original Message- From: VeeJay [mailto:maan...@gmail.com] Sent: dinsdag 28 april 2009 10:22 To: mysql@lists.mysql.com; VeeJay Subject: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug Hello guys and gurus I am keep getting this error after a while *1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug* Why don't you believe the mesage? It clearly says possible OS-dependent bug, so the OS is where to look.. Also, 35 is a pretty low number, so I expect it to be an errno as provided by the operating system. (Which agrees with the message text, that says errno 35.) I don't have FreeBSD available, on my Linux the code 35 means EDEADLK: /usr/include/asm-generic/errno.h:6:#define EDEADLK 35 /* Resource deadlock would occur */ For such searches, I have my tiny script FGIN: #! /bin/sh # # FGIN shell script to use fgrep on all /usr/include/h files # # $1, $2, ... options and arguments given to fgrep # # Simple example: #FGIN seteuid return the line(s) in system header files where # 'seteuid()' is defined or otherwise mentioned. # # 2004-09-13 Joerg Bruehe Initial published version find /usr/include -follow -name '*.h' -print | xargs fgrep -n $* As 35 my be a common term, I do FGIN 35 | fgrep errno I propose you do something similar on your machine. Granted, this still doesn't tell you what exactly happened, but it should help to narrow the possible causes. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer 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=mgai...@hotmail.com _ Rediscover Hotmail®: Get e-mail storage that grows with you. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Sto rage2_042009 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.
This is because you didn't copy innodb ibdata and ib_log files togeter. Or you forgot to stop mysqld when you remove its ib_log files. On Sat, Feb 7, 2009 at 7:21 AM, my sql mysql.g...@gmail.com wrote: WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. GOAL: Trying to restore mysql backup on different host using InnoDB backup that copes the backed up files to a files sever where I pulle them down to the new host I place all the MySQL datafiles and InnoDB log files in the same directory and all paths in the my.cnf files are pointing here upon the startup of the mysqld and the following crach recover that the Innodb do I get tons of this errors [see below] why? I do have the two InnoDB log files - so why does it complain that my DB 'may' be corrupt - I don't like this message. Event tried with seting innodb_force_recovery = 4 but I still get the error upon startup - why? It doesn't look like a clean startup to me 090206 14:56:34 InnoDB: Error: page 27060 log sequence number 4 1755884236 InnoDB: is in the future! Current system log sequence number 4 1682795020. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.
WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. GOAL: Trying to restore mysql backup on different host using InnoDB backup that copes the backed up files to a files sever where I pulle them down to the new host I place all the MySQL datafiles and InnoDB log files in the same directory and all paths in the my.cnf files are pointing here upon the startup of the mysqld and the following crach recover that the Innodb do I get tons of this errors [see below] why? I do have the two InnoDB log files - so why does it complain that my DB 'may' be corrupt - I don't like this message. Event tried with seting innodb_force_recovery = 4 but I still get the error upon startup - why? It doesn't look like a clean startup to me 090206 14:56:34 InnoDB: Error: page 27060 log sequence number 4 1755884236 InnoDB: is in the future! Current system log sequence number 4 1682795020. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files.
How do you backup HUGE tables?
We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 This can take a good amount of time to copy even just the file, and a mysqldump can take hours to export and import took 3 days (with the usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! So, it's not really feasible or ideal to do this daily. We'd like to do some kind of daily diff and weekly or monthly full backup. Also, as any good backup strategy involves off site backups, it's not very convenient or even feasible to transfer 80+ GB over our pipe ever day (which according to iperf is getting around 11 MBytes/sec from our data center to our office). Looking for ideas as to what others are doing to backup their very large sets? We do have a master/slave setup. We're thinking of adding two more slaves that are read-only and not accessed via the web at all. Just sits there being a backup effectively. One being offsite in another building and the logic that we'll trickle in maybe 100k per minute as the data is inserted into the real M/S so that should be negligible on our intra/internet. --- I've done some research here, but nothing stands out as the winner... but I'm open to any of these ideas if you can make a strong case for them. http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee per computer on which the MySQL server is run. http://www.innodb.com/hot-backup/ [not loving that it's a commercial tool] http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html http://dev.mysql.com/doc/refman/5.1/en/backup.html read the comments You can also take very fast online or hot backups if you have linux volume management or LVM ... I knew there was a way to use LVM for backups somehow. Maybe a solution for us? http://www.mysqlperformanceblog.com/?s=backup http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Some other backup solutions maybe: http://www.debianhelp.co.uk/mysqlscript.htm http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works so well, no need to improve it?) http://www.ozerov.de/bigdump.php
Re: How do you backup HUGE tables?
On Fri, Jan 23, 2009 at 4:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 Daevid, we have started working on an incremental/differential InnoDB backup tool. It is in need of a sponsor though. I'm betting that you don't change all 70GB of that table every day, and you'd appreciate being able to keep differentials and only do full backups every so often. For big datasets like this, dump is impossible or too expensive at some point. There are a lot of ways you could do this, but I'd recommend filesystem snapshots and binary copies. Unless you like long dumps and long restores... There might also be some higher-level strategies like archiving and purging or aggregation that would benefit you. These are the kinds of things I see pretty often and help people select good strategies, but it requires a lot of knowledge of your application to give good advice. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do you backup HUGE tables?
I would also suggest to use the innodb storage option 'innodb-file-per-table=ON' so that at least the datafile is split to have as many (smaller) datafiles as innodb tables. This could make it easier to deal with the whole database. Cheers Claudio Baron Schwartz wrote: On Fri, Jan 23, 2009 at 4:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 Daevid, we have started working on an incremental/differential InnoDB backup tool. It is in need of a sponsor though. I'm betting that you don't change all 70GB of that table every day, and you'd appreciate being able to keep differentials and only do full backups every so often. For big datasets like this, dump is impossible or too expensive at some point. There are a lot of ways you could do this, but I'd recommend filesystem snapshots and binary copies. Unless you like long dumps and long restores... There might also be some higher-level strategies like archiving and purging or aggregation that would benefit you. These are the kinds of things I see pretty often and help people select good strategies, but it requires a lot of knowledge of your application to give good advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do you backup HUGE tables?
Something totally ghetto that might work... If you could convert the files to appear to be text with some kind of reversible fast translation, rsync might be able to handle the diff part. You'd sure want to test this out thoroughly... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do you backup HUGE tables?
We have a very large, multi-terabyte database with individual tables that are over 100Gig. We have it on a Red Hat Linux system and we set up logical volumes, take LVM snapshots, then use rsync to move the data over. This works well and is a lot faster than dumping and certainly restore is faster. On Fri, Jan 23, 2009 at 3:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 This can take a good amount of time to copy even just the file, and a mysqldump can take hours to export and import took 3 days (with the usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! So, it's not really feasible or ideal to do this daily. We'd like to do some kind of daily diff and weekly or monthly full backup. Also, as any good backup strategy involves off site backups, it's not very convenient or even feasible to transfer 80+ GB over our pipe ever day (which according to iperf is getting around 11 MBytes/sec from our data center to our office). Looking for ideas as to what others are doing to backup their very large sets? We do have a master/slave setup. We're thinking of adding two more slaves that are read-only and not accessed via the web at all. Just sits there being a backup effectively. One being offsite in another building and the logic that we'll trickle in maybe 100k per minute as the data is inserted into the real M/S so that should be negligible on our intra/internet. --- I've done some research here, but nothing stands out as the winner... but I'm open to any of these ideas if you can make a strong case for them. http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee per computer on which the MySQL server is run. http://www.innodb.com/hot-backup/ [not loving that it's a commercial tool] http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html http://dev.mysql.com/doc/refman/5.1/en/backup.html read the comments You can also take very fast online or hot backups if you have linux volume management or LVM ... I knew there was a way to use LVM for backups somehow. Maybe a solution for us? http://www.mysqlperformanceblog.com/?s=backup http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Some other backup solutions maybe: http://www.debianhelp.co.uk/mysqlscript.htm http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works so well, no need to improve it?) http://www.ozerov.de/bigdump.php -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How do you backup HUGE tables?
I know how you feel! I think your two best options are these: 1.) Use LVM snapshots per the MPB links you mentioned as a guide. Your incremental backup would be the binary logs that MySQL writes. You could copy any of this data off site by mounting the snapshots and using your remote copy mechanizm of choice. 2.) Create a slave and create your backups from it, again using the binary logs as the incremental backup. You could also use the snapshot technique to create the initial data set for this host if you're not able to take this host down for an extended period of time. Claudio has an excellent point with innodb-file-per-table as well. Just make sure a single table will never grow to more than the maximum file size of your filesystem. Good luck, -Aaron On Fri, Jan 23, 2009 at 1:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 This can take a good amount of time to copy even just the file, and a mysqldump can take hours to export and import took 3 days (with the usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! So, it's not really feasible or ideal to do this daily. We'd like to do some kind of daily diff and weekly or monthly full backup. Also, as any good backup strategy involves off site backups, it's not very convenient or even feasible to transfer 80+ GB over our pipe ever day (which according to iperf is getting around 11 MBytes/sec from our data center to our office). Looking for ideas as to what others are doing to backup their very large sets? We do have a master/slave setup. We're thinking of adding two more slaves that are read-only and not accessed via the web at all. Just sits there being a backup effectively. One being offsite in another building and the logic that we'll trickle in maybe 100k per minute as the data is inserted into the real M/S so that should be negligible on our intra/internet. --- I've done some research here, but nothing stands out as the winner... but I'm open to any of these ideas if you can make a strong case for them. http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee per computer on which the MySQL server is run. http://www.innodb.com/hot-backup/ [not loving that it's a commercial tool] http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html http://dev.mysql.com/doc/refman/5.1/en/backup.html read the comments You can also take very fast online or hot backups if you have linux volume management or LVM ... I knew there was a way to use LVM for backups somehow. Maybe a solution for us? http://www.mysqlperformanceblog.com/?s=backup http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Some other backup solutions maybe: http://www.debianhelp.co.uk/mysqlscript.htm http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works so well, no need to improve it?) http://www.ozerov.de/bigdump.php
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
So where's the advantage of VARCHAR ? Less space on disc = less data retrieved from disc = faster data retrieval - sometimes. If you have small columns, a small number of rows, or both, then char columns may be faster. If you have large columns of varying actual length, lots of rows, or both, then varchar columns may be faster. I still think a CHAR field would be faster than a VARCHAR because of the fixed row length (assuming every thing else is fixed). Perhaps someone from the MySQL list could clarify...? -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- 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] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, Jan 7, 2009 at 2:26 PM, Richard Heyes rich...@php.net wrote: I still think a CHAR field would be faster than a VARCHAR because of the fixed row length (assuming every thing else is fixed). Perhaps someone from the MySQL list could clarify...? Say that your column length goes up to 2000 bytes, but on average is less than 512 bytes (ie. one disk block). What would be faster, reading 1 disk block (varchar), or reading 4 disk blocks (char) ? -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? On Wed, Jan 7, 2009 at 7:26 AM, Richard Heyes rich...@php.net wrote: So where's the advantage of VARCHAR ? Less space on disc = less data retrieved from disc = faster data retrieval - sometimes. If you have small columns, a small number of rows, or both, then char columns may be faster. If you have large columns of varying actual length, lots of rows, or both, then varchar columns may be faster. I still think a CHAR field would be faster than a VARCHAR because of the fixed row length (assuming every thing else is fixed). Perhaps someone from the MySQL list could clarify...? -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 4th) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On 1/7/09, Jim Lyons jlyons4...@gmail.com wrote: There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? That's my recollection, also, derived from a MySQL class. IIRC, the char length is equal to the longest varchar record in the column. David
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On Wed, Jan 7, 2009 at 9:17 AM, David Giragosian dgiragos...@gmail.com wrote: On 1/7/09, Jim Lyons jlyons4...@gmail.com wrote: There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? That's my recollection, also, derived from a MySQL class. IIRC, the char length is equal to the longest varchar record in the column. Actually it's a fixed-length buffer big enough to hold the worst-case possible value, not the worst-case existing value. In bytes, no less. If it's a utf8 varchar(100), that's 300 bytes, even if the biggest value in the table is one character. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
agquarx wants to keep up with you on Twitter
To find out more about Twitter, visit the link below: http://twitter.com/i/429c961b36cd7c31a28772a32e82bb19f302ebaf Thanks, -The Twitter Team About Twitter Twitter is a unique approach to communication and networking based on the simple concept of status. What are you doing? What are your friends doing—right now? With Twitter, you may answer this question over SMS, IM, or the Web and the responses are shared between contacts. This message was sent by a Twitter user who entered your email address. If you'd prefer not to receive emails when other people invite you to Twitter, click here: http://twitter.com/i/optout/1301c6a30a95fc0551a60625944c7c979f1be7b3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lands with sea view and beaches for sale, it worth try to see. Thank You!
!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 / titlenewsletter arpsworldproperties/title link href=news.css rel=stylesheet type=text/css / /head bodytable border=0 cellspacing=0 cellpadding=0 tr td colspan=3img src=imagens/header.jpg width=595 height=160 //td /tr tr td colspan=3 class=contactotituloOur contacts:/td /tr tr td colspan=3 class=contactospan class=contactoitalicotel:/span + 44 (0)208 200 60 20 |em span class=contactoitalicofax/span/emspan class=contactoitalico: /span+ 44 (0)208 200 10 49 | span class=contactoitalicom:/span + 44 (0)7938 056 756br / span class=contactoitalicoemail:/span a href=mailto:[EMAIL PROTECTED] class=contactoemail[EMAIL PROTECTED]/a | span class=contactoitalicowebsite:/span a href=http://arpsworldproperties.com; target=_blank class=contactositewww.arpsworldproperties.com/a/td /tr tr td colspan=3 class=textoitalicoWe are pleased in offering you 3 amazing opportunities for investment in Cape Verde.br / 3 amazing plots of land, exclusive to us for sale. br / We will assist in all aspects of the purchase until is completed. br / Before that, we will be too pleased in taking you there for a visit. /td /tr tr td colspan=3 class=textocallCall us now on (+ 44) 0208 200 60 20/td /tr tr td colspan=3 class=propriedadetituloCape Verde, Island of Santiago/td /tr tr td width=174 valign=top bgcolor=#DFFFBF class=imageimg src=imagens/property1.jpg width=174 height=129 //td td width=365 bgcolor=#DFFFBF class=propriedadesubtitulo1Tarrafal br / br / span class=propriedadesubtituloA 114,000 SQUARE METERS br / at â¬195 Euros/square meter /spanbr / span class=propriedadesubtituloBtotal price: â¬22,230,000/span/td td width=115 rowspan=2 bgcolor=#DFFFBF class=imageimg src=imagens/property1AA.jpg width=115 height=372 //td /tr tr td colspan=2 bgcolor=#DFFFBF class=textonormalp Payment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission. /p p span class=textoAlready Aproved development for resort: /span/p p class=texto Resort br / Casino br / Bungalowsbr / Hotel br / Apartments /p p Property has supply of Water, Electricity and Sewerage /p p class=texto At this time you cannot get a better deal for development than this, in Cape Verde /p/td /tr tr td colspan=3 valign=top bgcolor=#D2F4FF class=imageimg src=imagens/property2.jpg width=174 height=129 class=imageleft /span class=propriedadesubtitulo1Ponta Bomba/spanspan class=propriedadesubtituloitalicobr / (20 Km from Capital Praia)/spanbr / p class=propriedadesubtituloA322,5 Hectares (3,225,000 square meters) br / at just â¬12 Euros per square meter br / span class=propriedadesubtituloB total cost = â¬38,700,000/span/p p class=textonormalPayment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission./p/td /tr tr td colspan=3 valign=top bgcolor=#DFFFBFpimg src=imagens/property3.jpg width=174 height=129 class=imageleft /span class=propriedadesubtitulo120 KM from Capital Praia, /spanspan class=propriedadesubtituloitalico br / near the old town/span /p p class=propriedadesubtituloA900 hectares br / (can be divided in 2 plots of 450 hectares)br / span class=propriedadesubtituloB at only â¬3 Euros per square meter/span /p p class=textonormalAmazing plot of land with a total area of 900 hectares - 9,000,000 (nine million) square meters - in the vicinity of Porto Mosquito, Santiago Island, ideal for a super development in Cape Verde at a great price./p p class=textonormalPayment plan possibilities, all documentation transferred to buyer without additional costs + planning/development permission./p/td /tr tr td colspan=3nbsp;/td /tr tr td colspan=3 valign=top class=contactotituloDisclaimer:/td /tr tr td colspan=3 valign=top class=textoitalicospamThis email and its attachments have been sent by ARPS World Ltd and are not spam and may be confidential, being intended solely for the use of the individual to whom it is addressed. br / If you are not the intended recipient of this email and its attachments, you must take no action based upon them, nor must you copy or show them to anyone.br / If you believe you have received this email in error or if you wish to be excluded from our mailing list please send email to a href=mailto:[EMAIL PROTECTED] class=textoitalicospamemail[EMAIL PROTECTED]/a with subject title: âplease stop sending business mailsâ/td /tr /table /body /html -- MySQL General Mailing List For list archives: http
Re: Error: You can't specify target table '...' for update in FROM clause
On 07.02.2008 03:52 CE(S)T, Chris wrote: If you don't mind a mysql-specific fix, and can get the data you want from a select query you could: insert into table (select goes here) on duplicate key update; or maybe a replace into ? INSERT/REPLACE ... SELECT will always overwrite the entire row, but I only want to copy a single column of it. The rest of the record must remain intact. So I can't use that, too. I also try to avoid DBMS-specific workarounds where I can in this project. So maybe one day MySQL will drop the above mentioned restriction. :) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
On 06.02.2008 08:12 CE(S)T, Chris wrote: Yves Goergen wrote: My goal was to copy some potentially large BLOB from one record to another in the same table Update table set blob2_field=blob1_field; This does something totally different. ;) See my first posting why. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: You can't specify target table '...' for update in FROM clause
-Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 05, 2008 6:22 PM To: Baron Schwartz Cc: mysql@lists.mysql.com Subject: Re: Error: You can't specify target table '...' for update in FROM clause On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote: You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. [JS] I don't know if this is more efficient that copying the data to a program and pushing it back, but you can use a few statements to use a temporary table of your own. I often do this so that I can accomplish what I want without writing a program at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
Yves Goergen wrote: On 06.02.2008 08:12 CE(S)T, Chris wrote: Yves Goergen wrote: My goal was to copy some potentially large BLOB from one record to another in the same table Update table set blob2_field=blob1_field; This does something totally different. ;) See my first posting why. Ah I missed the first post. If you don't mind a mysql-specific fix, and can get the data you want from a select query you could: insert into table (select goes here) on duplicate key update; or maybe a replace into ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: You can't specify target table '...' for update in FROM clause
Hi, I've got an error message from MySQL 5.0 that I don't understand. UPDATE message_revision SET HasData = 1, Data = (SELECT Data FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5) WHERE MessageId = 7 AND RevisionNumber = 6 SQL error: [SQLSTATE:HY000, 1093] You can't specify target table 'message_revision' for update in FROM clause What went wrong? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
Hi, On Feb 5, 2008 11:26 AM, Yves Goergen [EMAIL PROTECTED] wrote: Hi, I've got an error message from MySQL 5.0 that I don't understand. UPDATE message_revision SET HasData = 1, Data = (SELECT Data FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5) WHERE MessageId = 7 AND RevisionNumber = 6 SQL error: [SQLSTATE:HY000, 1093] You can't specify target table 'message_revision' for update in FROM clause What went wrong? You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) However, you can do multi-table updates like this: UPDATE tbl AS a INNER JOIN tbl AS b ON SET a.col = b.col If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance: UPDATE tbl SET col = ( SELECT ... FROM (SELECT FROM) AS x); The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote: You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. However, you can do multi-table updates like this: UPDATE tbl AS a INNER JOIN tbl AS b ON SET a.col = b.col That sounds interesting, however, I couldn't find it in PostgreSQL's and SQLite's reference. Is this a MySQL extension over the SQL standard? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
Yves Goergen wrote: On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote: You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. Update table set blob2_field=blob1_field; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Computerworld reporter would like to hear from you ASAP today, on MySQL,acquisition by Sun...
Todd, I'd be happy to answer all of your questions. See my responses in-line below: What are your impressions of this deal? I think it is fantastic because it means that even more money will be made and this is what Sun's top priority is and represents what they care about, realistically. This is why I use commercial software instead of open-source. Is this good for the MySQL community? Absolutely, Todd. It will help get the errant programmers back on-track with sound software design and solid methodologies. Being able to rely on the sage judgment and wisdom of Sun's proven software developers and technological leadership will only make the community stronger and the products better. Good for MySQL users? Todd, I can't express how much better it will be for end-users! If you take a look at one of my other posts to this very mailing list, you'll see a broad strategy that I've outlined for the future of MySQL. I've put forth a number of proposals including a new MySQL-FS file system, new cross-platform administrative tools, and a renewed focus on the Java language and how it can be used to help MySQL along the road to success. Do you see a downside here? Not yet I don't! Overall, is this a good thing for MySQL and its users or a bad thing? As I've outlined, I think it's a fantastic first-step towards bringing MySQL out of the 20th century and into the 21st. This is nothing but the beginning of a bold new era in the annals of MySQL history. Together, Sun, MySQL, and the community can bring about the change that is needed. Please tell me your thoughts in as much detail as possible. Be sure, please to give me your FULL NAME, Job Title, PHONE NUMBER, e-mail address and what you do for a living -- whether programmer, consultant, etc. My name is Billy B. Bilano. I am the Director of Information Technology at a Major US Bank. If you need anything more, please contact me directly by emailing [EMAIL PROTECTED] and I'll get back to you in short order. Thanks so much for your quick reply! Thank you, Todd, for helping me share with your readers! -- Mr. Billy B. Bilano, MSCE, CCNA, CISSP, and now QISP http://www.bilano.biz/ Expert Sysadmin Since 2003! 'C:\WINDOWS, C:\WINDOWS\GO, C:\PC\CRAWL' -- RMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Computerworld reporter would like to hear from you ASAP today on MySQL acquisition by Sun...
Hello, I'm a reporter at Computerworld magazine and I'm writing a story today on MySQL user and developer reax to the Sun acquisition of MySQL. Can you drop me a note and let me know your thoughts about this ASAP? I'd like to know: What are your impressions of this deal? Is this good for the MySQL community? Good for MySQL users? Do you see a downside here? Overall, is this a good thing for MySQL and its users or a bad thing? Please tell me your thoughts in as much detail as possible. Be sure, please to give me your FULL NAME, Job Title, PHONE NUMBER, e-mail address and what you do for a living -- whether programmer, consultant, etc. Thanks so much for your quick reply! Best wishes, Todd Weiss Computerworld Todd R. Weiss General Assignment Reporter Computerworld Voice: 717-560-5255 [EMAIL PROTECTED] http://www.computerworld.com Computerworld -- The Voice of IT Management
Re: MySQL give me the following error:Do you already have another mysqld server running on port: 3306 ?
try this command to shutdown mysql mysqladmin --host=abc --password shutdown On 12/9/07, peri [EMAIL PROTECTED] wrote: Hi, I installed MysqL 4.1.22 on windows vista. When I try to run any command in Mysql/MySQL server 4.1/bin path it gives me the following error : C:\Program Files\MySQL\MySQL Server 4.1\binmysqld-max-nt --standalone Can't start server: Bind on TCP/IP port: Invalid argument 071207 22:50:57 [ERROR] Do you already have another mysqld server running on port: 3306 ? 071207 22:50:57 [ERROR] Aborting 071207 22:50:57 [Note] mysqld-max-nt: Shutdown complete Also when I try to run mysqladmin shutdown command it gives me the following error C:\Program Files\MySQL\MySQL Server 4.1\binmysqladmin shutdown mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'ODBC'@'localhost' (using password: NO)' I cannot run any command on mysql. Can anyone help me on this issue? How can I solve this problem Thank you Perihan -- View this message in context: http://www.nabble.com/MySQL-give-me-the-following-error%3ADo-you-already-have-another-mysqld-server-running-on-port%3A-3306---tp14236918p14236918.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL give me the following error:Do you already have another mysqld server running on port: 3306 ?
Hi, I installed MysqL 4.1.22 on windows vista. When I try to run any command in Mysql/MySQL server 4.1/bin path it gives me the following error : C:\Program Files\MySQL\MySQL Server 4.1\binmysqld-max-nt --standalone Can't start server: Bind on TCP/IP port: Invalid argument 071207 22:50:57 [ERROR] Do you already have another mysqld server running on port: 3306 ? 071207 22:50:57 [ERROR] Aborting 071207 22:50:57 [Note] mysqld-max-nt: Shutdown complete Also when I try to run mysqladmin shutdown command it gives me the following error C:\Program Files\MySQL\MySQL Server 4.1\binmysqladmin shutdown mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'ODBC'@'localhost' (using password: NO)' I cannot run any command on mysql. Can anyone help me on this issue? How can I solve this problem Thank you Perihan -- View this message in context: http://www.nabble.com/MySQL-give-me-the-following-error%3ADo-you-already-have-another-mysqld-server-running-on-port%3A-3306---tp14236918p14236918.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HOW DO YOU LOCATE AND REMOVE ROGUE QUERIES FROM EXECUTING?
I started using MySQL 5 a couple of months ago and I was able to handle most of the issues OK. However there is this particular problem that is causing untold misery for my self and particularly my users. At unexpected times, a roque query is executed or triggered all on its accord and starts deleting a certain group of records. I remember manually entering executing a query like DELETE * from LEDGER_ACCOUNTS where LED_Type = BFW; from the MySQL GUI browser program some time ago. I have never used that query again and yet this query had been executed intermittently a number of times already. I have tried restarting the MySQL server a number of times but still no success. The question is, how do I locate this rogue query and remove it so it doesn't executed at all? Aibo Bebes papua New Guinea Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you allow external computers to access server instance?
Mysql restricts access outside the server after the installation (on not
all, but several cases) so i suggest to connect to the mysql database as
root and review the host values on the user table, that can give you an
idea of who is allowed and from where is allowed ...remeber that % means
anywhere.
If you need more information i suggest to read the manual searching by
users permissions.
Carlos
Ferindo Middleton wrote:
I found how to bind to addrees to but didn't find anything in my.ini
about
skip-networking but now I have this problem where the I can't connect
locally sitting at the computer using hostname localhost if I type in
the IP address of the computer I get a messsage saying {Hostname} is not
allowed to connect to this MySQL server
Ferindo
On 6/21/07, Baron Schwartz [EMAIL PROTECTED] wrote:
Hi,
Ferindo Middleton wrote:
I've installed MySQL5 on a machine running Windows XP. I'm not an
advanced
user so I chose all the default configuration settings when I went
through
the setup wizard for the server instance. The database works fine
and I
can
access it when I'm sitting at the computer through the command line
client
and MySQL Query Browser. However, if I try to access the server
instance
from another computer on my LAN via MySQL Query Browser, I get a
message
saying the connection is refused How do I configure the server to
allow
incoming connections from other computers on my network?
Try to configure bind-address to the server's IP address, and ensure
skip-networking is not defined.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do you allow external computers to access server instance?
Don't forget to allow port 3306 (or whatever port you're server listens on)
through any Windows firewall...
Tim
-Original Message-
From: Carlos Proal [mailto:[EMAIL PROTECTED]
Sent: Friday, June 22, 2007 1:40 PM
Cc: mysql@lists.mysql.com
Subject: Re: How do you allow external computers to access server
instance?
Mysql restricts access outside the server after the installation (on not
all, but several cases) so i suggest to connect to the mysql database as
root and review the host values on the user table, that can give you an
idea of who is allowed and from where is allowed ...remeber that % means
anywhere.
If you need more information i suggest to read the manual searching by
users permissions.
Carlos
Ferindo Middleton wrote:
I found how to bind to addrees to but didn't find anything in my.ini
about
skip-networking but now I have this problem where the I can't
connect
locally sitting at the computer using hostname localhost if I type
in
the IP address of the computer I get a messsage saying {Hostname} is
not
allowed to connect to this MySQL server
Ferindo
On 6/21/07, Baron Schwartz [EMAIL PROTECTED] wrote:
Hi,
Ferindo Middleton wrote:
I've installed MySQL5 on a machine running Windows XP. I'm not an
advanced
user so I chose all the default configuration settings when I went
through
the setup wizard for the server instance. The database works fine
and I
can
access it when I'm sitting at the computer through the command line
client
and MySQL Query Browser. However, if I try to access the server
instance
from another computer on my LAN via MySQL Query Browser, I get a
message
saying the connection is refused How do I configure the server to
allow
incoming connections from other computers on my network?
Try to configure bind-address to the server's IP address, and ensure
skip-networking is not defined.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you allow external computers to access server instance?
I've installed MySQL5 on a machine running Windows XP. I'm not an advanced user so I chose all the default configuration settings when I went through the setup wizard for the server instance. The database works fine and I can access it when I'm sitting at the computer through the command line client and MySQL Query Browser. However, if I try to access the server instance from another computer on my LAN via MySQL Query Browser, I get a message saying the connection is refused How do I configure the server to allow incoming connections from other computers on my network? -- Ferindo Middleton -Sleekcollar-
Re: How do you allow external computers to access server instance?
Hi, Ferindo Middleton wrote: I've installed MySQL5 on a machine running Windows XP. I'm not an advanced user so I chose all the default configuration settings when I went through the setup wizard for the server instance. The database works fine and I can access it when I'm sitting at the computer through the command line client and MySQL Query Browser. However, if I try to access the server instance from another computer on my LAN via MySQL Query Browser, I get a message saying the connection is refused How do I configure the server to allow incoming connections from other computers on my network? Try to configure bind-address to the server's IP address, and ensure skip-networking is not defined. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you allow external computers to access server instance?
I found how to bind to addrees to but didn't find anything in my.ini about
skip-networking but now I have this problem where the I can't connect
locally sitting at the computer using hostname localhost if I type in
the IP address of the computer I get a messsage saying {Hostname} is not
allowed to connect to this MySQL server
Ferindo
On 6/21/07, Baron Schwartz [EMAIL PROTECTED] wrote:
Hi,
Ferindo Middleton wrote:
I've installed MySQL5 on a machine running Windows XP. I'm not an
advanced
user so I chose all the default configuration settings when I went
through
the setup wizard for the server instance. The database works fine and I
can
access it when I'm sitting at the computer through the command line
client
and MySQL Query Browser. However, if I try to access the server instance
from another computer on my LAN via MySQL Query Browser, I get a message
saying the connection is refused How do I configure the server to
allow
incoming connections from other computers on my network?
Try to configure bind-address to the server's IP address, and ensure
skip-networking is not defined.
Baron
--
Ferindo Middleton
Web Application Developer/Database Administrator/IT Infrastructure and
Integration Management Specialist/Perception Augmentation and Control
Supplementation Research Specialist for AI Wetware-to-Software Interface and
Design
-Sleekcollar-
How do you reference custom column names
SELECT 1+1 as foo, 2 as bar, foo+bar This will not work, but I think you can see what I am trying to do. I need to run a pretty hefty update on a database, and there are some pretty heavy calculations I will be doing. The result of many of those, needs to be further used to make updates on other columns. Can someone suggest a method to do this so my SQL is not so non-manageable? Something like this: SELECT price*tax_rate+something-other Then I need to use the result of the above calc right away in some other field, which would be: SELECT price*tax_rate+something-other, ((price*tax_rate+something-other)+.6) Where what I really want to do is: SELECT price*tax_rate+something-other as foo, (foo+.6) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you reference custom column names
In the last episode (Jun 19), Scott Haneda said: SELECT 1+1 as foo, 2 as bar, foo+bar This will not work, but I think you can see what I am trying to do. I need to run a pretty hefty update on a database, and there are some pretty heavy calculations I will be doing. The result of many of those, needs to be further used to make updates on other columns. Can someone suggest a method to do this so my SQL is not so non-manageable? User variables? mysql SELECT @foo:=1+1 as foo, @bar:=2 as bar, @[EMAIL PROTECTED] as foobar; +-+-++ | foo | bar | foobar | +-+-++ | 2 | 2 | 4 | +-+-++ -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Would you like to explore Opportunity with Microsoft.......
Hi, This is with reference of your profile for regarding job opportunities with Microsoft IDC, Hyderabad. As you know Microsoft is on hiring spree and IDC is Microsoft's second biggest development center and is currently working on 35 products and technologies including Windows Vista, Visual Studio, Office Mobile, MS-CRM, MSN, Office Live Meeting and Windows Serviceability. IDC may be thousands of miles away from Redmond (US), but it's mission remains the same: To enable people and businesses throughout the world realize their full potential . Microsoft reached its 1000th landmark of total number of employees in June and still expanding on a rapid pace keeping itself focused on only quality resources and most evolved process of interview. Below is the link providing information on all the product units and the products being developed by them in IDC. http://www.microsoft.com/india/indiadev/default.aspx Please reply me back with your consent and updated profile so as we can initiate the process soon. The information passed on to us will be kept confidential. Also let me know, if you have been interviewed by Microsoft Earlier. If yes, please mention when. In case you have some queries then mail me back with your contact number and we can fix some time to discuss over phone. Regards, Shikha Saxena Recruitment Executive
Re: We would like to link to you
Um... Did everyone get this message? Quoting: Dear Sirs, We contacted you last week, because we would like to exchange links with you. We understand that there is often not enough time in the day for everything, but, I am sure you appreciate that getting targeted links to your website is “key” to high search engine rankings. I am sure you will agree that this is a very good trade for both our companies as it will improve both of our rankings in the search engines. Go to: http://www.offshorebusinessportal.com/link-to-us--add-url-c170.html Your link will be active on our website within 24 hours from your reply. Regards Matt Silicia (Webmaster) -- Glen Barber signature.asc Description: Digital signature
Re: We would like to link to you
Yes. :) And the one before it. On May 29, 2007, at 2:14 PM, Glen Barber wrote: Um... Did everyone get this message? Quoting: Dear Sirs, We contacted you last week, because we would like to exchange links with you. We understand that there is often not enough time in the day for everything, but, I am sure you appreciate that getting targeted links to your website is “key” to high search engine rankings. I am sure you will agree that this is a very good trade for both our companies as it will improve both of our rankings in the search engines. Go to: http://www.offshorebusinessportal.com/link-to-us--add-url- c170.html Your link will be active on our website within 24 hours from your reply. Regards Matt Silicia (Webmaster) -- Glen Barber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We would like to link to you
Ok... just making sure. Spambots search mailing-lists often, I suppose? signature.asc Description: Digital signature
