Authenticate MySQL-Users using PAM
Im currently setting up a LAMP server which will be accessed by (a lot of) students for various courses. To handle user accounts I use PAM to authenticate ftp-logins against an LDAP server. Additionally, I'm looking for a way to authenticate MySQL users against PAM (or LDAP directly) too, so I would not be forced to manage passwords in 'mysql.user' seperatly. Is there anybody aware of a patch to MySQL to do this? Regards, Bernhard -- Bernhard Fiser [EMAIL PROTECTED] Telekommunikation und Medien Fachhochschule St. Pölten/St. Poelten University of Applied Sciences Herzogenburger Straße 68 | 3100 St. Pölten | +43 (0) 2742 313228 48 pgpwxHs9WTr6I.pgp Description: PGP signature
Re: How big is too big?
Misao schrieb: Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? We have a few 20-30GB-InnoDB-Tables (growing) without any problems (mysql 4.1.5gamma). The limits of mysql are somewhere in the terabyte-area I think, there is information on that in the manual. I assume your problem would probably be hardware/performance at some point. The machine that we are running that big database on is a dual-Opteron, 8gigs of RAM, 750GB RAID 1+0 SATA-Hotswap. no problems so far... nice piece of hardware ;) I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Seems like a problem of MySQL Administrator. Check if you use the newest version, else change your frontend or make a bug-report. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Kevin A. Burton wrote: Jason J. W. Williams wrote: Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Define DoS? - Denial of Service... - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Ian Sales (DBA) wrote: Kevin A. Burton wrote: Define DoS? - Denial of Service... ug... Thats not what I meant... I mean what type of behavior were you noticing? Just all connections being occupied on the server? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How big is too big?
The maximum single file size of a system is as follows, *Operating System* *File-size Limit* Linux 2.2-Intel 32-bit 2GB (LFS: 4GB) Linux 2.4 (using ext3 filesystem) 4TB Solaris 9/1016TB NetWare w/NSS filesystem8TB win32 w/ FAT/FAT32 2GB/4GB win32 w/ NTFS 2TB (possibly larger) MacOS X w/ HFS+ 2TB (cut and pasted from mysql.com per table size) and heres a nice tutorial on how to figure out your database size via php http://www.webmasterworld.com/forum88/2069.htm if you dont do php, its basicaly a script that says, SHOW TABLE STATUS; and then adds up the data_lenght index_lenght of each table within a database. Regards, Marcus Joyce Jan Kirchhoff wrote: Misao schrieb: Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? We have a few 20-30GB-InnoDB-Tables (growing) without any problems (mysql 4.1.5gamma). The limits of mysql are somewhere in the terabyte-area I think, there is information on that in the manual. I assume your problem would probably be hardware/performance at some point. The machine that we are running that big database on is a dual-Opteron, 8gigs of RAM, 750GB RAID 1+0 SATA-Hotswap. no problems so far... nice piece of hardware ;) I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Seems like a problem of MySQL Administrator. Check if you use the newest version, else change your frontend or make a bug-report. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data in different tables or is one big table just as fast?
We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. Any help would be great. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. However, if you can use MyISAM tables, you should at least consider using MERGE tables: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html If your database is well indexed and your searches can generate relatively small amounts of data on the first key used, the overhead of having enormous files is small. At the other end, if the MySQL is reduced to a linear search, the impact of a huge file is enormous. If most of your SELECTs are on recent data, it is work considering splitting the table into daily, weekly, or monthly sub-tables. This means that you can archive very old months, and pack recent months, while keeping only the current month active. But if you need to search the entire archive frequently, this will reduce performance. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search in mulitple-table query
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the
elements of the WHERE clause at the end of the query I sent before.
I've gotten good results with the following version (it breaks all
the fields in the Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
Create a fulltext index on 3 columns and search :
MATCH (t6.course_keywords, t6.course_description,
t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside
that still has me confused. If I just slightly alter the order of
all the various sub-clauses in the WHERE portion of the query, I get
some courses that violate the requirement course_type_code='MJ'
(last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right
after the WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
I think you must use ():
WHERE (
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
)
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later
requirements, such as the ones on course_start_date,
course_end_date, and so on to the end.
Is there a requirement as to which order sub-clauses of a WHERE
clause have to follow? I couldn't find anything that described
this, but I'm perfectly willing to admit I have a hard time finding
lots of things in the online docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing [EMAIL PROTECTED] wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB. I'd like to be able to do a fulltext search on fields in
several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table? What would be the correct syntax for
such
a query? Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a
trivial question, I can't seem to find an answer in the documentation
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a
AMD64 and thread stack size
It looks like the default thread stack is not large enough for MySQL 4.1 on Linux running on AMD64. With NPTL, the database quickly crashed after a few hours of heavy load (segv). Without NTPL, I believed it wasn't the case. But after one week mysqld process were stuck in a strange state. They weren't doing anything, just as if they were waiting for a dead lock. It didn't happen with MySQL 4.0 on the same hardware. Bumping up the thread stack to 256 K seems to fix the issue, both with and without NPTL. Best regards, -Frank. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
undelete data
I accidently deleted a number of data records (83 records) from a table using 'delete' SQL command. I want to recover the deleted data. I checked the table with 'myisamchk'. the result is as follows. myisamchk seitext.MYI Checking MyISAM file: seitext.MYI Data records: 7 Deleted blocks: 86 myisamchk: warning: 2 clients is using or hasn't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links MyISAM-table 'seitext.MYI' is usable but should be fixed Is it possible to recover the deleted data ? How? My mySQL is running on Redhat Linux and version 4.01. min
show full processlist question
Hi I didn´t find it in the documentation I would like to know if the collumn time in the command show full processlist show the seconds the query is taking to execute!? Thank´s in advance - Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador do Yahoo! agora.
InnoDB engine as default for an entire database
Dear friend, is it possible to select the InnoDB engine as default for an entire database? What we want is to create all InnoDB tables, without using the declaration type=innodb. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How big is too big?
[snip] ...big... [/snip] We are running OpenBSD servers utilizing a dual Xeon processor architecture with a 1TB RAID. We have a database using MyISAM tables...here are some stats from phpMyAdmin on one database on this server -- 20 table(s) Sum 347,092,085 --149.6 GB Two tablea in this database -- tblClass10 128,310,773 MyISAM 40.3 GB tblClass11 205,420,404 MyISAM 97.4 GB We do experience delays in performing some complex queries, but even in these cases MySQL consumes less than 50% of server resources during processing. We have a set of complex queries that takes approximately 30-45 minutes when run each week, including UPDATES. Big is only determined by 2 things in my experience, physical resources and the ability to manage the database well. There are several companies (Yahoo comes to mind IIRC) that have MySQL databases far larger and more complex than the ones I use each day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb transactions
Hi
I am using innodb tables to implement transactions on my system
here is the pseudo code:
SET AUTOCOMMIT = 0
BEGIN
if(erro){
ROLLBACK
} else {
COMMIT
}
When i do commit a transaction and list the data im my web aplication the
records are not acurate .
How can i get the rigth data ?
I search on documentation and find somthing like read comited how can make a
select using read commited if ths clause is not on the select sintax?
Tnak´s in advance
-
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador do Yahoo!
agora.
Circumventing the max_allowed_packet limit?
Hi MySQL ppl,
I recently ran into some problems with uploading large files through a
HTML form and then putting these files into a MySQL BLOB field. I read
about the server parameter max_allowed_packet which turned out to be the
problem.
My script is going to be redistributed to it needs to run smoothly on
many different servers. Therefore I'm now looking into ways of avoiding
this problem, and I also want to avoid forcing the script users to
reconfigure his MySQL server.
Now, my questions are:
Q1. Can I divide my query into several packets and thus insert the large
BLOB without exceeding the max_allowed_packet limit?
Q2. I doodled around a little bit with the SET command and I was able to
change the session version of the max_allowed_packet server parameter.
The insert query still failed though, even if I did successfully execute
the SET max_allowed_packet=16MB query and also double checked it with an
extra select afterwards; and it had indeed changed. See the code below.
The question is, is this is viable method? Is the local/session version
of this variable just ignored by the server?
My code (for Q2):
$result = mysql_query(SELECT @@local.max_allowed_packet AS
max_allowed_packet;);
$row = mysql_fetch_assoc($result);
echo max_allowed_packet= . $row['max_allowed_packet'] . \n;
$result = mysql_query(SET @@local.max_allowed_packet=1600;);
if (!$result) {
echo It did not work. . mysql_errno() . : . mysql_error() . \n;
}
$result = mysql_query(SELECT @@local.max_allowed_packet AS
max_allowed_packet;);
$row = mysql_fetch_assoc($result);
echo max_allowed_packet= . $row['max_allowed_packet'] . \n;
mvh
martin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export database to XML/UML/Webpage?
Hello, I am looking to export a mysql database to a gui type interface. Basically i'm looking at a big database and I wanna dump it to a webpage/uml where I can add notes on what each table is and what it does, even just dumping to a webpage would be fine cause i can edit from there. is there anything like this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How big is too big?
The problem isn't really if the database fits into RAM, it's if your index does, if your searches use the index. Databases can easily outgrow even the maximum RAM you could install on even a high end machine. So while it's ideal to fit your database in RAM, it's not always feasible. Your first big hit will be from an index not being able to be cached in RAM, then you require disk access for all your searches on that index. That said, you will most likely hit the limits of your database design before you hit the limit of MySQL. If your database data doesn't change much, just gets added to, you should look at dimensional database design as opposed to traditional relational design. This starts getting you into the realm of datawarehousing, which has different design rules. Two books that may be helpful are: High Performance MySQL from O'Reillly Designing Effective Database Systems from Addison Wesley The first book would be helpful for scaling MySQL, the second will be helpful for creating a scalable design. On Jan 27, 2005, at 7:08 PM, Misao wrote: Our database server has 4Gigs of ram on it, and we have a hard time of figuring out the true InnoDB settings on how to use that ram up. I ended up just increasing the ram used until it just wouldn't run anymore, then backed up and used that. We plan on adding another 4Gigs of ram, total of 8GB. Tweaking the InnoDB stuff doesn't seem as easy as the MyISAM side. So as it stands, we have 4GB, and one table that easily exceeds that, almost 8GB in size. Even after we bump the server up to 8GB, that means this table will barely fit. Does that mean we need to start cleaning out that table, or adding new ram? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB engine as default for an entire database
default-storage-engine=INNODB in [mysqld] section of the config file will make InnoDB default for a server. -Original Message- From: symbulos partners [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 8:03 AM To: mysql@lists.mysql.com Subject: InnoDB engine as default for an entire database Dear friend, is it possible to select the InnoDB engine as default for an entire database? What we want is to create all InnoDB tables, without using the declaration type=innodb. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1006: Can't create database
I was told to try this: chown mysql.mysql -R /var/lib/mysql chmod 750 -R /var/lib/mysql and it worked afterward. So that's the datadir permission problem. How can find the user mysql password? The hosting company which I got the dedicated server package from set that up of course. Should I go to phpAdmin and reset that password so that I know what it is from now on? Thanks again! Jordan Tom Crimmins wrote: [snip] I'm using version 3.23.58. I tried to create a database foo using phpAdmin(logged in as root) and got: ERROR 1006: Can't create database 'foo'. (errno: 13) ... drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql [/snip] perror 13 Error code 13: Permission denied File permissions look ok at that level, and I would assume that mysql user can get to that directory. You could login to your linux box as root then 'su - mysql' and see if you can create a directory in the mysql datadir as the mysql user. This isn't a grant table issue because I believe that will give you an access denied error. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa
DB recovery
Hello, I have a server here that ran MySQL 3.23.x This box recently crashed as a result of a power outage (possible surge, my surge protector may have failed) The box doesn't boot up, but the HD is ok... all my docs are there.. So, I'm wondering if it's somehow possible to get the MySQL 3 files and recreate them on another box we have here that runs MySQL 4.0. Is this possible? It wouldn't be as simple as getting the files from the dead box and putting them in the new box, would it? Thanks, -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: MySQL - install failure on XP Toshiba laptop
This was sent to me, off list. I am just forwarding it to it's original targets. -- Shawn - Forwarded by Shawn Green ([EMAIL PROTECTED]) on 01/28/2005 09:00 AM - Kyle Elmblade [EMAIL PROTECTED] wrote on 01/27/2005 04:59:28 PM: Hello Simon, I ran accross the post below in the MySQL forums, and I'm too lazy to sign up and post. I might have an answer for you. It piques my interest any time I see a is not a valid win32 application in conjuction with an installed service. I support an application that installs several services. All of these services reside, by default, in c:\program files\app name\servicename.exe. Funny thing about services when Windows tries to start them up. It acts asif it is attempting to start them from a command prompt, and consequently, having a space in the path can be an issue. For 99% of the world, it's never a problem. For the unlucky 1%, it can be a vexing, yet agonizingly simple issue. The root of the problem turns out to be with a file named program, sitting in the root of the c:\ drive. This file does not have an extension, and may or may not even contain data in it. However, when Windblows (no, that's not a typo) goes looking for the application, it gets as far as program,sees that there is nothing right after the m, and checks first to see if it matches a file rather than a folder. Surprise, it finds a file named program, and consequently attempts to execute that program. ZAP, you get an error. Anyway, it's worth checking on. If it does solve your problem, I would really appreciate it if you could let me know. I'm curious to see if it's the same thing. Sincerely, Kyle Elmblade [EMAIL PROTECTED] * Thanks for your suggestions, everyone, but I have followed the manual and repeated the installation process (with it's very nice GUI, by the way) more than once, and I still have a problem. Perhaps it isn't that simple after all. I had similar problem on this laptop when I installed version 4, and I see now the same message in event logs that The MySQL service failed to start due to the following error: MySQL is not a valid win32 application. I had no problem installing and running version 4 of the server on a desktop running XP. So I think the issue is with the laptop. At the beginning of the laptop installation XP whines that MySQL is not digitally signed or something, but when I instruct it to ignore that the installation commences. I noticed that the MySQL service records the old location of the executable for the server but you can't edit that. So I chose MySQL4 as the service name during re-installation -but it refuses to create the service.So I chose port 3307 in case that was causing a conflict -but still it refuses to create the service. If anyone thinks they know the solution to this problem (be it trivial or not) I'll take the risk that the answer might confuse me, because it's not in the manual. Thanks for any help.
Core exam
On the Pearson Vue website, two MySQL Core exams are listed: 001-002 and 001-003. Which do I choose for the 4.0 exam before February 1? -- Vincent Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1006: Can't create database
[snip] How can find the user mysql password? The hosting company which I got the dedicated server package from set that up of course. Should I go to phpAdmin and reset that password so that I know what it is from now on? [/snip] The mysql linux user should not be able to login interactivly, just as they should not have a shell. To do anything as the mysql user, simply become root, then 'su - mysql'. You will not be asked for a password. example: [EMAIL PROTECTED] tom]$ su - Password: [EMAIL PROTECTED] root]# su - mysql -bash-2.05b$ id uid=100(mysql) gid=101(mysql) groups=101(mysql) -bash-2.05b$ --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB recovery
[snip] This box recently crashed as a result of a power outage (possible surge, my surge protector may have failed) The box doesn't boot up, but the HD is ok... all my docs are there.. So, I'm wondering if it's somehow possible to get the MySQL 3 files and recreate them on another box we have here that runs MySQL 4.0. Is this possible? It wouldn't be as simple as getting the files from the dead box and putting them in the new box, would it? [/snip] If these were myisam tables, assuming the files didn't get badly damaged, you should be able to copy the directory for each database you need to recover into the mysql datadir on the new host. You may have to use myisamchk to repair the indexes. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html You will want to do all of this with mysqld stopped. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1006: Can't create database
Thanks! That makes senses. And once I chmod as instructed, I can create directory in the mysql folder. Tom Crimmins wrote: [snip] How can find the user mysql password? The hosting company which I got the dedicated server package from set that up of course. Should I go to phpAdmin and reset that password so that I know what it is from now on? [/snip] The mysql linux user should not be able to login interactivly, just as they should not have a shell. To do anything as the mysql user, simply become root, then 'su - mysql'. You will not be asked for a password. example: [EMAIL PROTECTED] tom]$ su - Password: [EMAIL PROTECTED] root]# su - mysql -bash-2.05b$ id uid=100(mysql) gid=101(mysql) groups=101(mysql) -bash-2.05b$ --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB engine as default for an entire database
Hello. You can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the storage_engine or table_type system variable. See: http://dev.mysql.com/doc/mysql/en/storage-engines.html symbulos partners [EMAIL PROTECTED] wrote: Dear friend, is it possible to select the InnoDB engine as default for an entire database? What we want is to create all InnoDB tables, without using the declaration type=innodb. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cascade on delete problem
Hello.
Your syntax shouldn't work in the MySQL server (comas at the end of CREATE TABLE
statments). What type of storage engine do you use? You can see it with the
following
statement:
SHOW CREATE TABLE MENU_GROUP;
[snip]
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH
GRANT
OPTION;
CREATE DATABASE builder2;
use builder2;
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP (
id int NOT NULL,
parent_id int NOT NULL DEFAULT '0',
sort int,
visible VARCHAR(1) NOT NULL DEFAULT 'T',
);
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id);
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T');
menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T');
UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T');
TABLE MENU_TYPE (
id INT NOT NULL AUTO_INCREMENT,
attribute_type varchar(200) NOT NULL,
primary key (id)
);
INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name');
CREATE TABLE MENU_GROUP_REL (
menu_type varchar(200),
data_id int NOT NULL,
display_name varchar(250),
link varchar(250),
);
ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(data_id)
REFERENCES
MENU_GROUP(id) ON DELETE CASCADE;
INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp');
INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities');
INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp');
INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp');
INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp');
INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp');
INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday',
'opensunday.jsp');Scott Purcell [EMAIL PROTECTED] wrote:
[snip]
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install error - unable to wite to /tmp/root/
Hello. Not enough information to make a conclusion. Please, send us information about MySQL and operating system versions. What value does your environment variable TMPDIR have? [snip] I am having a problem starting up mysql. I am unable to run the mysql_install_db routine. When I run it I keep getting the error - unable to write to /tmp/root/... Originally I got the error unable to write to /tmp but that has since changed to the first error. I'm not a newbee to IT or Linux. I tried chmod to change rights but that doesn't work. I'm forgetting something. Help. MichaelMichael Parker [EMAIL PROTECTED] wrote: [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circumventing the max_allowed_packet limit?
Hello.
Q2. I doodled around a little bit with the SET command and I was able to
change the session version of the max_allowed_packet server parameter.
Both the client and the server have their own max_allowed_packet variable,
so if you want to handle big packets, you must increase this variable both
in the client and in the server. See:
http://dev.mysql.com/doc/mysql/en/packet-too-large.html
Martin Olsson [EMAIL PROTECTED] wrote:
Hi MySQL ppl,
I recently ran into some problems with uploading large files through a
HTML form and then putting these files into a MySQL BLOB field. I read
about the server parameter max_allowed_packet which turned out to be the
problem.
My script is going to be redistributed to it needs to run smoothly on
many different servers. Therefore I'm now looking into ways of avoiding
this problem, and I also want to avoid forcing the script users to
reconfigure his MySQL server.
Now, my questions are:
Q1. Can I divide my query into several packets and thus insert the large
BLOB without exceeding the max_allowed_packet limit?
Q2. I doodled around a little bit with the SET command and I was able to
change the session version of the max_allowed_packet server parameter.
The insert query still failed though, even if I did successfully execute
the SET max_allowed_packet=16MB query and also double checked it with an
extra select afterwards; and it had indeed changed. See the code below.
The question is, is this is viable method? Is the local/session version
of this variable just ignored by the server?
My code (for Q2):
$result = mysql_query(SELECT @@local.max_allowed_packet AS
max_allowed_packet;);
$row = mysql_fetch_assoc($result);
echo max_allowed_packet= . $row['max_allowed_packet'] . \n;
$result = mysql_query(SET @@local.max_allowed_packet=1600;);
if (!$result) {
echo It did not work. . mysql_errno() . : . mysql_error() . \n;
}
$result = mysql_query(SELECT @@local.max_allowed_packet AS
max_allowed_packet;);
$row = mysql_fetch_assoc($result);
echo max_allowed_packet= . $row['max_allowed_packet'] . \n;
mvh
martin
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld restarts with no apparent reason
Hello. /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source distribution 050125 15:58:44 mysqld restarted The mysqld_safe script writes to error log that mysqld was restarted. So I don't see any useful in error log (InnoDB messages may say something to Heikki Tuuri, but I think that there is no information for us, because server works fine about several hour sometimes). So we should follow recomendations at: http://dev.mysql.com/doc/mysql/en/crashing.html If your server is not heavy-loaded - try to enable debugging. See: http://dev.mysql.com/doc/mysql/en/debugging-server.html Then make the trace and core files (--debug --core-file). Check that there are no memory leaks with top command. Can you switch to 4.1.9 and try to reproduce the problem? Try running the tests in the mysql-test directory and the MySQL benchmarks to find out on which test crashes mysqld. Do you use InnoDB tables? If so, disable InnoDB. Also run mysqld with --skip-networking option to reduce problems related to buggy gethostbyname() implementations. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: non-unique key of multiple columns
Hello. I am not sure if the key is only built on a single col ( I added Use SHOW INDEX or SHOW CREATE TABLE statements. See: http://dev.mysql.com/doc/mysql/en/show-index.html http://dev.mysql.com/doc/mysql/en/show-create-table.html I think you should send your query, the output of EXPLAIN statement and the definitions of your tables which are used by query. Ginger Cheng [EMAIL PROTECTED] wrote: Hello, MySQL gurus, I have a non-unique key of 2 columns, the 1st is a varchar(15), 2nd is a int(10) unsigned. But when I 'explain' a query that uses this key, the key_len is only 15 with the key name shown up in the 'key' column of this table correctly though. According to the manual, that means how many columns of a key is accually used in the query. So the query is only using the 1st column of my key although it could not be efficient without using the 2nd column. I am not sure if the key is only built on a single col ( I added the index as 'ALTER TABLE a ADD INDEX (f, s)') or it is the MySQL optimizer that decides it will only use the 1st column of the key. Is there any way to check and get it right? Thank you all for help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy newbie question re: option file and passwords
Hello. The mysql program uses user sebyte and password for user ddj. When mysql starts it calls for load_defaults(), which put the arguments from your config file before the command line options that you specified. And than uses the last given argument. It looks like mysql was invoked with -hlocalhost -uroot -px -hlocalhost -usebyte Sebastian Tennant [EMAIL PROTECTED] wrote: Hi there, For some reason, despite entering my passwords in ~/.my.cnf, I still have to enter a password on the command line to login to mysql. I have tried this with each of the accounts I have created and all return the same error: $ mysql -u sebyte ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What am I doing wrong? Here are the accounts I have created, and below that is my ~/.my.cnf. I have 'x'ed out my actual passwords in this post but is there something wrong with the syntax I am using. I have verified that the file IS being read each time I attempt to login. Any help much appreciated. TIA Sebastian mysql select host,user,password from user; +---+--+--+ | host | user | password | +---+--+--+ | localhost | root | 4be7c493348ee750 | | localhost | guest| | | localhost | debian-sys-maint | 7985ba067899ea77 | | localhost | sebyte | 5722c7a41e81cbb3 | | localhost | ddj | 7b17b74d22ac2a88 | +---+--+--+ 5 rows in set (0.08 sec) # -*- mode: shell-script -*- # ~/.my.cnf # [mysql] host=localhost user=root password='xx' host=localhost user=sebyte password='x' host=localhost user=ddj password='xxx' [mysqladmin] host=localhost user=root password='xx' -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export database to XML/UML/Webpage?
Mike Jennings [EMAIL PROTECTED] wrote on 01/28/2005 07:57:06 AM: Hello, I am looking to export a mysql database to a gui type interface. Basically i'm looking at a big database and I wanna dump it to a webpage/uml where I can add notes on what each table is and what it does, even just dumping to a webpage would be fine cause i can edit from there. is there anything like this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Mike, With all due respect, web pages (HTML documents and their ilk) are generally information that is simply consumed by a visitor to the site and are rarely used as control or design documents. UML documents (use cases, sequence diagrams, etc) are generally not as good at database schema modelling as ER diagrams. So, since your choice of output media seems inappropriate to me for the task you describe, I will have to guess at what I think you want to accomplish. What it sounds like to me is that you want to have some way to fill in the COMMENT information for each table (and possibly even for each field) in order to document what each of them represents. You can do that manually with ALTER TABLE statements and you should be able to do that through any GUI-type database administration program. Once you fill in the COMMENT information, I know you can quickly get a simple text dump of just your database schema (all of the CREATE xxx statements, not the data) by calling the utility program mysqldump with the correct options. Have you looked into using the MySQL Administrator yet? It's a GUI program that should do most of what you want (I know it doesn't diagram but it's somewhere to start). You will have to search around to find some other ER-based GUI administrator tools because I can't think of any off the top of my head (sorry! no coffee yet!). Maybe others on the list will recommend some? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
2 gigs limits on MyISAM indexes?
Is there a limit on the size of .MYI files? I have a database that worked flawlessly until today. I can't restart it, it immediately freezes. I noticed that the .MYI file of a table has reached exactly 2 gigs. May it be related? Is there anything to do in order to recover the data and to keep the server working? Best regards, -Frank. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Export database to XML/UML/Webpage?
[[snip]] Have you looked into using the MySQL Administrator yet? It's a GUI program that should do most of what you want (I know it doesn't diagram but it's somewhere to start). You will have to search around to find some other ER-based GUI administrator tools because I can't think of any off the top of my head (sorry! no coffee yet!). Maybe others on the list will recommend some? Shawn Green Fabforce's DBDesigner 4 is fantastic... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Export database to XML/UML/Webpage?
As long as you've only got 1 instance of Mysql and it's on the default port! Otherwise use one of the MySQL products. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk Fabforce's DBDesigner 4 is fantastic... ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB recovery
Thanks, That was pretty easy... And, thank you mysql! ;-) Yves On Fri, 28 Jan 2005 08:14:37 -0600, Tom Crimmins [EMAIL PROTECTED] wrote: [snip] This box recently crashed as a result of a power outage (possible surge, my surge protector may have failed) The box doesn't boot up, but the HD is ok... all my docs are there.. So, I'm wondering if it's somehow possible to get the MySQL 3 files and recreate them on another box we have here that runs MySQL 4.0. Is this possible? It wouldn't be as simple as getting the files from the dead box and putting them in the new box, would it? [/snip] If these were myisam tables, assuming the files didn't get badly damaged, you should be able to copy the directory for each database you need to recover into the mysql datadir on the new host. You may have to use myisamchk to repair the indexes. http://dev.mysql.com/doc/mysql/en/myisamchk-syntax.html You will want to do all of this with mysqld stopped. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very slow select with join
Hello, I am clueless of this query, becaouse it's very slow - between 30 and 60 seconds: mysql SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid - FROM orders o - LEFT JOIN editor e1 ON o.createdby = e1.id - LEFT JOIN editor e2 ON o.changedby = e2.id - LEFT JOIN customer c ON o.customerid = c.id - LEFT JOIN product p ON o.productid = p.id - LEFT JOIN brand m ON o.brandid = m.id - LEFT JOIN calendar cal ON cal.id = o.scheduleId - LEFT JOIN editor e3 ON cal.engineerid = e3.id - LEFT JOIN partner pr ON e3.partnerid = pr.id - JOIN order_acl a ON a.objid = o.id a.gid IN (3, 4, 302, 303, - 312) - ORDER BY o.id DESC - LIMIT 20; (...table...) 20 rows in set (28.66 sec) When I try get data from table order_acl only it's quick: mysql SELECT DISTINCT objid - FROM order_acl - WHERE gid IN (1, 2, 3, 4, 213, 214, 5021) - LIMIT 20; (...table...) 20 rows in set (0.30 sec) And when I try this select (without JOIN order_acl only) it's quick too: mysql SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid - FROM orders o - LEFT JOIN editor e1 ON o.createdby = e1.id - LEFT JOIN editor e2 ON o.changedby = e2.id - LEFT JOIN customer c ON o.customerid = c.id - LEFT JOIN product p ON o.productid = p.id - LEFT JOIN brand m ON o.brandid = m.id - LEFT JOIN calendar cal ON cal.id = o.scheduleId - LEFT JOIN editor e3 ON cal.engineerid = e3.id - LEFT JOIN partner pr ON e3.partnerid = pr.id - ORDER BY o.id DESC - LIMIT 20; (...table...) 20 rows in set (1.11 sec) Do you know what's wrong? :( mysql EXPLAIN SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS serialid - FROM orders o - LEFT JOIN editor e1 ON o.createdby = e1.id - LEFT JOIN editor e2 ON o.changedby = e2.id - LEFT JOIN customer c ON o.customerid = c.id - LEFT JOIN product p ON o.productid = p.id - LEFT JOIN brand m ON o.brandid = m.id - LEFT JOIN calendar cal ON cal.id = o.scheduleId - LEFT JOIN editor e3 ON cal.engineerid = e3.id - LEFT JOIN partner pr ON e3.partnerid = pr.id - ORDER BY o.id DESC - LIMIT 20; ++-+---++---+-+-+-+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+-+---+-+ | 1 | SIMPLE | o | ALL| NULL | NULL|NULL | NULL| 20402 | Using temporary; Using filesort | | 1 | SIMPLE | e1| eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.createdby| 1 | Using index; Distinct | | 1 | SIMPLE | e2| eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.changedby| 1 | Using index; Distinct | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.customerid | 1 | Using index; Distinct | | 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.productid| 1 | Using index; Distinct | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.o.brandid | 1 | Using index; Distinct | | 1 | SIMPLE | cal | eq_ref | id| id | 8 | servis_info3.o.scheduleid | 1 | Distinct| | 1 | SIMPLE | e3| eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.cal.engineerid | 1 | Distinct| | 1 | SIMPLE | pr| eq_ref | PRIMARY | PRIMARY | 8 | servis_info3.e3.partnerid | 1 | Using index; Distinct | ++-+---++---+-+-+-+---+-+ 9 rows in set (0.30 sec) -- Best Regards Pavel Novak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT index on InnoDB tables
Dear friends, I would need to index FULLTEXT two columns in a InnoDB table. How do you solve the problem? -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb transactions
Hello.
In my opinion, you should use SERIALIZABLE transaction isolation level or
SELECT ... LOCK IN SHARE MODE.
?ngelo M. Rigo [EMAIL PROTECTED] wrote:
[-- text/plain, encoding 8bit, charset: iso-8859-1, 25 lines --]
Hi
I am using innodb tables to implement transactions on my system
here is the pseudo code:
SET AUTOCOMMIT = 0
BEGIN
if(erro){
ROLLBACK
} else {
COMMIT
}
When i do commit a transaction and list the data im my web aplication the
records are not acurate .
How can i get the rigth data ?
I search on documentation and find somthing like read comited how can make a
select using read commited if ths clause is not on the select sintax?
Tnak?s in advance
-
Yahoo! Acesso Gr?tis - Internet r?pida e gr?tis. Instale o discador do Yahoo!
agora.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables
Hello, I would need to index FULLTEXT two columns in a InnoDB table. How do you solve the problem? As you have noticed - you cannot. So, all you can do is creating a MyISAM table and copying the column contents. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
On Friday 28 Jan 2005 15:41, you wrote: As you have noticed - you cannot. So, all you can do is creating a MyISAM table and copying the column contents. Are you suggesting to have a full copy of the table in MyISAM format? Is there any other workaround? The reason because we are using InnoDB is because there s full support - for foreign keys, - for joint queries - for rollback on commit Does anybody know any other way of indexing the table in a way, which would allow full text search? -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 gigs limits on MyISAM indexes?
What Operating System are you running this on? Also, is there anything in the errorlog? Is there a limit on the size of .MYI files? I have a database that worked flawlessly until today. I can't restart it, it immediately freezes. I noticed that the .MYI file of a table has reached exactly 2 gigs. May it be related? Is there anything to do in order to recover the data and to keep the server working? Best regards, -Frank. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 gigs limits on MyISAM indexes?
On Fri, Jan 28, 2005 at 04:00:24PM +, Mat wrote: What Operating System are you running this on? Linux 2.6, 64 bits. MySQL 4.1.9. Also, is there anything in the errorlog? Nothing, but as soon as I restart the server, it enters a strange state where all slots are full with unauthenticated connections that never die. No real query seems to be running any more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners [EMAIL PROTECTED] wrote on 01/28/2005 11:14:00 AM: On Friday 28 Jan 2005 15:41, you wrote: As you have noticed - you cannot. So, all you can do is creating a MyISAM table and copying the column contents. Are you suggesting to have a full copy of the table in MyISAM format? No, only the BLOB/TEXT columns need to be moved to MyISAM. Is there any other workaround? The reason because we are using InnoDB is because there s full support - for foreign keys, - for joint queries - for rollback on commit The MyISAM table type also fully supports JOIN queries. More importantly, for you, it supports full-text indexes. What many people have done to solve the problem you present has been to split the original table into two pieces. All BLOB/TEXT fields and a field ID are moved to a MyISAM table while the other fields stay in your original InnoDB table. This has a distinct performance advantage, too. If you run a query that retrieves only non-(BLOB/TEXT) fields from a table that has BLOB/TEXT columns defined (any table type), then all of the BLOB/TEXT data is read with the rest of the row data off of the disk into memory for every row not eliminated by an index, just to be ignored for the final output. By splitting your heavy fields into a separate table, you will avoid transferring all of that data into memory each and every time you only need light data (numerics, chars, varchars, etc) from the row. This results in less disk I/O, less memory consumption, and fewer CPU cycles for each and every query that doesn't need to use or retrieve the BLOB/TEXT data. What you lose with this design is, as you pointed out, the ability to rollback changes to the text fields when a transaction fails. You could not define foreign keys on BLOB/TEXT fields anyway(only against portions of those fields) so you are not losing much that way. However since BLOB/TEXT data is usually not as frequently updated as the other fields, you may be just fine without that level of protection. Does anybody know any other way of indexing the table in a way, which would allow full text search? Not for InnoDB tables. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Full text search in mulitple-table query
On Fri, 28 Jan 2005, Santino wrote:
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten good
results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
t1.course_title,
t1.course_subtitle,
t1.course_brochure_path,
t2.course_start_date,
t2.course_end_date,
t4.location_name1,
t4.location_name2,
t4.location_city,
t4.location_state,
t5.allow_online_registration,
t6.course_keywords
FROM
cme_course_info.tblCourses t1
LEFT JOIN cme_course_info.tblCourseDates t2
ON t1.course_id = t2.course_id
LEFT JOIN cme_course_info.tblCourseLocations t3
ON t1.course_id = t3.course_id
LEFT JOIN cme_course_info.tblLocations t4
ON t3.location_record = t4.location_record
LEFT JOIN cme_course_info.tblCourseWebSwitches t5
ON t1.course_id = t5.course_id
LEFT JOIN cme_course_info.tblCourseExtraInfo t6
ON t1.course_id = t6.course_id
WHERE
t1.course_webready='1'
AND
t3.primary_location='1'
AND
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
Create a fulltext index on 3 columns and search :
MATCH (t6.course_keywords, t6.course_description,
t6.course_intended_audience) AGAINST ('care')
So, does seearching on multiple columns only work if you create the
fulltext index on all of them at the same time? When I read the docs they
seemed to imply that indeces created on multiple columns wouldn't be
individually searchable. In other words, if I create a fulltext index on
col1, col2, and col3, then I will not be able to match against only col1.
I created the three as separate indeces so that I could maintain the
ability to search each separately at some point. Am I wrong to do so?
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_type_code='MJ'
ORDER BY
t2.course_start_date, t2.course_end_date, t1.course_title;
This pretty much ends my problem, except for one interesting aside that
still has me confused. If I just slightly alter the order of all the
various sub-clauses in the WHERE portion of the query, I get some courses
that violate the requirement course_type_code='MJ' (last part of WHERE)
Specifically, if I take the MATCH parts and move them up to right after the
WHERE, like so:
WHERE
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
I think you must use ():
Yep, that fixed the problem, now I can put the ORed portion of the filter
at the front, where it logically seems to belong and all works just fine.
Thanks for the assist!
WHERE (
MATCH (t6.course_keywords) AGAINST ('care')
OR
MATCH (t6.course_description) AGAINST ('care')
OR
MATCH (t6.course_intended_audience) AGAINST ('care')
OR
MATCH (t1.course_title) AGAINST ('care')
)
AND
t2.course_start_date'2005-02-01'
AND
t2.course_end_date'2005-12-31'
AND
t1.course_webready='1'
AND
t3.primary_location='1'
AND
t1.course_type_code='MJ'
Suddenly, I get courses showing up that violate all the later requirements,
such as the ones on course_start_date, course_end_date, and so on to the
end.
Is there a requirement as to which order sub-clauses of a WHERE clause have
to follow? I couldn't find anything that described this, but I'm perfectly
willing to admit I have a hard time finding lots of things in the online
docs.
Thanks for any lucidity anyone can lend,
Cris
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Log data transfer amount?
I've scoured the MySQL manuals... does anyone know if there's any way to log the amount of data that individual queries produce? Eg: like bytes transferred in a web server log? - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 gigs limits on MyISAM indexes?
This may be of use to you: http://dev.mysql.com/doc/mysql/en/table-size.html It appears that there is no limit in MySQL itself, but maybe in the unlying operating system. Frank Denis (Jedi/Sector One) wrote: On Fri, Jan 28, 2005 at 04:00:24PM +, Mat wrote: What Operating System are you running this on? Linux 2.6, 64 bits. MySQL 4.1.9. Also, is there anything in the errorlog? Nothing, but as soon as I restart the server, it enters a strange state where all slots are full with unauthenticated connections that never die. No real query seems to be running any more.
COMPLICATED UPDATE
how do I make a query that does this? update tbl_a set location_code=0 where tbl_a.country_id = (select id from countries where has_zones=0) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
As you have noticed - you cannot. So, all you can do is creating a MyISAM table and copying the column contents. Are you suggesting to have a full copy of the table in MyISAM format? No, only the BLOB/TEXT columns need to be moved to MyISAM. Is there any other workaround? The reason because we are using InnoDB is because there s full support - for foreign keys, - for joint queries - for rollback on commit The MyISAM table type also fully supports JOIN queries. More importantly, for you, it supports full-text indexes. What many people have done to solve the problem you present has been to split the original table into two pieces. All BLOB/TEXT fields and a field ID are moved to a MyISAM table while the other fields stay in your original InnoDB table. This has a distinct performance advantage, too. If you run a query that retrieves only non-(BLOB/TEXT) fields from a table that has BLOB/TEXT columns defined (any table type), then all of the BLOB/TEXT data is read with the rest of the row data off of the disk into memory for every row not eliminated by an index, just to be ignored for the final output. Then again - this particular problem is more a MySQL internal problem that simply should be fixed :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: list of error codes
Note that this error list is for the Linux version (parts differs for another OS). More error descriptions can be found in the header files. (forgot currently which ones). If you search the forum for error codes and my name than you will find the info (roughly a year+ old) The typical place for the error codes is usually /usr/include/errno.h But that tends to be references to OS specific places. On Linux the actual numbers/mappings for i386 are in /usr/include/asm/errno.h Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: COMPLICATED UPDATE
[snip] how do I make a query that does this? update tbl_a set location_code=0 where tbl_a.country_id = (select id from countries where has_zones=0) [/snip] Read about multi-table updates here http://www.mysql.com/update -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COMPLICATED UPDATE
That depends on what version of MySQL you are using. MySQL 4.1+ has support for subselects, so you could do what you have there. I'm assuming that you're running 4.1, otherwise you would have tried that code and found it to work... You may have to get the result of (select id from countries where has_zones=0) first, store it in a variable, then update tbl_a set location_code=0 where tbl_a.country_id = your_variable Diana Castillo wrote: how do I make a query that does this? update tbl_a set location_code=0 where tbl_a.country_id = (select id from countries where has_zones=0) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
Thanks for the extremely useful answer. Some comments, questions here below. On Friday 28 Jan 2005 16:38, you wrote: No, only the BLOB/TEXT columns need to be moved to MyISAM. Yes, I thought of doing so. The drawback is that you de-normalise the database. Is that correct? There are 2 alternatives we should then consider: - extract the two important columns, put them in a separate MyISAM table, index the separate table, operate searches only on the separate table. Like having a view, but permanent. - separate the original table, in two tables (columns which need to be indexed, columns which do not need to be indexed), operate the search only on the table with the relevant columns. I do not like it too much, because it spoils the structure of the database. From a logical point of view, the former is better. Furthermore, there is rollback. (By the way, how do you solve the rollback problem?) The latter consumes less disk space, performance wise is better. Do you see any other drawback / advantage? -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
Thanks for the extremely useful answer. Some comments, questions here below. On Friday 28 Jan 2005 16:38, you wrote: No, only the BLOB/TEXT columns need to be moved to MyISAM. Yes, I thought of doing so. The drawback is that you de-normalise the database. Is that correct? There are 2 alternatives we should then consider: - extract the two important columns, put them in a separate MyISAM table, index the separate table, operate searches only on the separate table. Like having a view, but permanent. - separate the original table, in two tables (columns which need to be indexed, columns which do not need to be indexed), operate the search only on the table with the relevant columns. I do not like it too much, because it spoils the structure of the database. From a logical point of view, the former is better. Furthermore, there is rollback. (By the way, how do you solve the rollback problem?) The latter consumes less disk space, performance wise is better. Do you see any other drawback / advantage? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 gigs limits on MyISAM indexes?
What does the error log say? Anything? Donny -Original Message- From: Frank Denis (Jedi/Sector One) [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 10:42 AM To: Mat Cc: mysql@lists.mysql.com Subject: Re: 2 gigs limits on MyISAM indexes? On Fri, Jan 28, 2005 at 04:00:24PM +, Mat wrote: What Operating System are you running this on? Linux 2.6, 64 bits. MySQL 4.1.9. Also, is there anything in the errorlog? Nothing, but as soon as I restart the server, it enters a strange state where all slots are full with unauthenticated connections that never die. No real query seems to be running any more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
On Fri, 28 Jan 2005 11:47:47 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 LIMIT 10 However, if you can use MyISAM tables, you should at least consider using MERGE tables: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html I will. If your database is well indexed and your searches can generate relatively small amounts of data on the first key used, the overhead of having enormous files is small. At the other end, if the MySQL is reduced to a linear search, the impact of a huge file is enormous. I'm not sure I understand this. If most of your SELECTs are on recent data, it is work considering splitting the table into daily, weekly, or monthly sub-tables. This means that you can archive very old months, and pack recent months, while keeping only the current month active. But if you need to search the entire archive frequently, this will reduce performance. I only need to go through the entire archive when indexing via Swish or when Google stops by. Thanks for your help. Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COMPLICATED UPDATE
Diana Castillo [EMAIL PROTECTED] wrote on 01/28/2005 11:53:20 AM: how do I make a query that does this? update tbl_a set location_code=0 where tbl_a.country_id = (select id from countries where has_zones=0) The UPDATE statement (as of 4.0.4) allows you to update multiple tables at once. (All of the JOIN methods are supposed to be valid but you cannot use ORDER BY or LIMIT with a multitable update) http://dev.mysql.com/doc/mysql/en/update.html So, if we wrote a query just to look at the rows you want to UPDATE (without using a subselect), you could write: SELECT * FROM tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id AND countries.has_zones = 0; To translate that into an UPDATE statement, all we need to do is a little re-arranging: UPDATE tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id AND countries.has_zones = 0 SET location_code=0; The FROM tables become the UPDATE tables. If we had a where clause, it remains the same. Here is another way to write the same SELECT statement: SELECT * FROM tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id WHERE countries.has_zones = 0; And this would be the alternative UPDATE: UPDATE tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id SET location_code=0 WHERE countries.has_zones = 0; If this doesn't work for you (old version?) write back and we can work up something else that will. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Easy newbie question re: option file and passwords
On Fri, 28 Jan 2005 09:47:24 +0200, Gleb Paharenko wrote: Hello. The mysql program uses user sebyte and password for user ddj. When mysql starts it calls for load_defaults(), which put the arguments from your config file before the command line options that you specified. And than uses the last given argument. It looks like mysql was invoked with -hlocalhost -uroot -px -hlocalhost -usebyte I see... Thanks a lot. Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixed with Fields
I was wondering if there is a way to create fixed width fields. Example: The field is set to 18 but data contained is 11. I need the length to remain 18. Is there anyway to do this? Thanks for your help. -Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fixed with Fields
Use char DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Marc Michalowski [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 10:28 AM To: mysql@lists.mysql.com Subject: Fixed with Fields I was wondering if there is a way to create fixed width fields. Example: The field is set to 18 but data contained is 11. I need the length to remain 18. Is there anyway to do this? Thanks for your help. -Marc -- 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]
Date Conversion on Mysql 4.0.2
Hi, Can some help me with a date conversion problme that I am having. I have a date data in the following format: 'Month Days Year Hour:Minute AM/PM' Example: 'Aug 21, 2004 2:00 PM' So far the hours that I have spent have been in vain. I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? TIA. Minh = Minh La __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion on Mysql 4.0.2
Minh La wrote: So far the hours that I have spent have been in vain. Next time a couple of minutes with the Fine Manual instead? :-) I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? Quoting the FM: STR_TO_DATE() is available as of MySQL 4.1.1. FWIW, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed with Fields
Dathan Pattishall wrote: Use char And use fixed-length types for *all* columns... one variable-length column makes all records variable-length. Nick -Original Message- From: Marc Michalowski [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 10:28 AM To: mysql@lists.mysql.com Subject: Fixed with Fields I was wondering if there is a way to create fixed width fields. Example: The field is set to 18 but data contained is 11. I need the length to remain 18. Is there anyway to do this? Thanks for your help. -Marc -- 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]
Why MySQL is very slow in dropping indexes?
Dropping an index on a MyISAM table should be instantaneous. It should only take as long as deleting the idx file from the file system. But it's taking almost as long as creating the index itself! Here's my queries and time they took: /*[10:58:17 AM][367172 ms]*/ alter table MyTable add index (MyIndex) /*[11:20:21 AM][183891 ms]*/ alter table MyTable drop index MyIndex In MS SQL server, if the index isn't clustered and there are no other indexes in the table, dropping the index is instantaneous. This extreme slowness in dropping a simple index in MySQL defeats the whole strategy of dropping indexes on some tables before a huge insert operation. Why doesn't it just delete the index file and clear whatever cached buffers it has of it? __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why MySQL is very slow in dropping indexes?
Homam S.A. [EMAIL PROTECTED] wrote on 01/28/2005 02:27:51 PM: Dropping an index on a MyISAM table should be instantaneous. It should only take as long as deleting the idx file from the file system. But it's taking almost as long as creating the index itself! Here's my queries and time they took: /*[10:58:17 AM][367172 ms]*/ alter table MyTable add index (MyIndex) /*[11:20:21 AM][183891 ms]*/ alter table MyTable drop index MyIndex In MS SQL server, if the index isn't clustered and there are no other indexes in the table, dropping the index is instantaneous. This extreme slowness in dropping a simple index in MySQL defeats the whole strategy of dropping indexes on some tables before a huge insert operation. Why doesn't it just delete the index file and clear whatever cached buffers it has of it? You neglected to mention which version of MySQL you are running. This behavior may have been fixed in newer versions as yours is not the first post I can remember seeing on this general topic. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Why MySQL is very slow in dropping indexes?
Homam S.A. wrote: This extreme slowness in dropping a simple index in MySQL defeats the whole strategy of dropping indexes on some tables before a huge insert operation. See http://dev.mysql.com/doc/mysql/en/alter-table.html , especially these bits: Note that if you use any other option to ALTER TABLE than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column). We plan to fix this in the future, but because ALTER TABLE is not a statement that is normally used frequently, this isn't high on our TODO list. As of MySQL 4.0, this feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. You want to DISABLE, not DROP, the keys. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why MySQL is very slow in dropping indexes?
Homam S.A. wrote: Dropping an index on a MyISAM table should be instantaneous. It should only take as long as deleting the idx file from the file system. But it's taking almost as long as creating the index itself! Here's my queries and time they took: /*[10:58:17 AM][367172 ms]*/ alter table MyTable add index (MyIndex) /*[11:20:21 AM][183891 ms]*/ alter table MyTable drop index MyIndex In MySQL, most ALTER TABLE operations involve a full reconstruction of the table, so dropping a key often takes almost as long as adding one. For a long time, optimizing them has not been a priority. Also note that in MyISAM all keys are stored in one MYI file. So dropping a key is not as easy as just deleting a file. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Conversion on Mysql 4.0.2
Minh La wrote: Hi, Can some help me with a date conversion problme that I am having. I have a date data in the following format: 'Month Days Year Hour:Minute AM/PM' Example: 'Aug 21, 2004 2:00 PM' So far the hours that I have spent have been in vain. I tried using str_to_date, but it keeps failing. Looks like it's not in version 4.0.2? Minh: Your options are: * upgrade to 4.1 * parse and convert the date in your applications * use an ugly combination of SUBSTRING() and CASE to parse out the date * write a UDF implementing STR_TO_DATE() * backport STR_TO_DATE() to 4.0 -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why MySQL is very slow in dropping indexes?
This extreme slowness in dropping a simple index in MySQL defeats the whole strategy of dropping indexes on some tables before a huge insert operation. See http://dev.mysql.com/doc/mysql/en/alter-table.html , especially these bits: Note that if you use any other option to ALTER TABLE than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column). We plan to fix this in the future, but because ALTER TABLE is not a statement that is normally used frequently, this isn't high on our TODO list. As of MySQL 4.0, this feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. You want to DISABLE, not DROP, the keys. Which, btw, is very misleading -- non-unique indices are NOT keys. They're indices. I would only consider unique constraints and primary key constraints actual keys. :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners wrote: Is there any other workaround? The reason because we are using InnoDB is because there s full support - for foreign keys, - for joint queries - for rollback on commit Does anybody know any other way of indexing the table in a way, which would allow full text search? Sure -- use an external search engine that has database hooks (or create your own connector). Depending on the sophistication (or existence) of a database connector for the search engine, you'll have to write more or less code to tell it how them to talk to each other -- how to know when there's new data to index, how to retrieve the text data into the search engine for indexing or display. The most sophisticated ones use database triggers to make it all fairly easy. Otherwise, you'll need to write code that hands the text and a pointer (typically the primary key) to the full-text engine when a record is added or modified, and the pointer for deletes. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
Jacob Friis Larsen wrote: On Fri, 28 Jan 2005 11:47:47 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 LIMIT 10 Jacob: The above query should be very fast as long as you have a key on feedid even if the table is very large. I see one problem with it, though - in this form it is not guaranteed to give you the most recent items. You need to add an ORDER BY some timestamp DESC for it to work right. I would, however, be seriously concerned about diskspace if a table is adding 200 MB a day with no archiving/compression/purges. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed with Fields
Are you talking about display width? Use the RPAD function:
http://dev.mysql.com/doc/mysql/en/string-functions.html
mysql SELECT RPAD('foo', 20, 'x') AS foo;
+--+
| foo |
+--+
| foox |
+--+
Eamon Daly
- Original Message -
From: Marc Michalowski [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, January 28, 2005 12:27 PM
Subject: Fixed with Fields
I was wondering if there is a way to create fixed width fields. Example:
The field is set to 18 but data contained is 11. I need the length to
remain 18. Is there anyway to do this? Thanks for your help.
-Marc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners wrote: Thanks for the extremely useful answer. Some comments, questions here below. On Friday 28 Jan 2005 16:38, you wrote: No, only the BLOB/TEXT columns need to be moved to MyISAM. Yes, I thought of doing so. The drawback is that you de-normalise the database. Is that correct? No, I believe if the database was in the third normal form, it would still be. You just split the entity into two sub-entities that logically share the primary key. But that does not really matter. Normalization is a theory. If using it helps you create an application that is fast, uses less resources, and is easy to maintain then stick to it. If it gets in the way of reaching your goals, it is not the right theory for your application. There are 2 alternatives we should then consider: - extract the two important columns, put them in a separate MyISAM table, index the separate table, operate searches only on the separate table. Like having a view, but permanent. - separate the original table, in two tables (columns which need to be indexed, columns which do not need to be indexed), operate the search only on the table with the relevant columns. I do not like it too much, because it spoils the structure of the database. From a logical point of view, the former is better. Furthermore, there is rollback. (By the way, how do you solve the rollback problem?) Now you are asking difficult questions. MyISAM tables do not know about rollback, so you have to fake it, but you never have a real one. You can try to take care of it in your application by deleting or restoring the modified rows. But if I were in your shoes, I would ask at this point about how big of a deal it is in your application to be able to roll back your blob. And, of course, another option is to contact Heikki and coax him into hiring somebody to add FULLTEXT to InnoDB tables. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very slow select with join
From: Pavel Novak | 1 | SIMPLE | o | ALL| NULL | NULL|NULL | NULL| 20402 | Using temporary; Using filesort | Well, this is a clue IMHO... no suitable indexes in o could be found to use in this query... Since you use a lot of columns from orders, you'll probably need an index that contains all the columns from orders that are used in the query, or at least an index that can be used for the slowest join. Furthermore you sort on orders.id, so this field should be included in the index. Creating the right indexes and putting the parts of the query in the right order with the help of the output from explain, can make a slow query fly! Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
