MySQL doesn't work. I tried to modified the line: innodb_data_file_path = ibdata1:10M:autoextend
to innodb_data_file_path = ibdata1:500M or innodb_data_file_path = ibdata1:500M:autoextend or innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend they all gave me the same error below. InnoDB: Error: data file ./ibdata1 is of a different size InnoDB: than specified in the .cnf file! InnoDB: Could not open data files 031105 9:42:56 Can't init databases 031105 09:42:56 mysqld ended The last one really does the matter!! That's if I run out of the space on the current directory, I won't be able to put another file anywhere else!? Leo Nitin wrote: >You're right, it wont decrease the physical size, but only free up the space >within file to optimize the tablespace, in case, you want to check the size >of this data file, you can remove autoextend from: > >innodb_data_file_path = ibdata1:10M:autoextend > >and specify the size limit in the place of 10M, but i guess, if you specify >the size to less than 790M (which is the current size of your datafile), to >say 500M and the space is free in that file, it will resize it. That's the >behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, >and let me know as i dont have my database on innodb yet. > >'Tablespace is part of your database. database consists of at least one >tablespace. it's basically used to restrict users from seeing other user's >data. like, you can assign a tablespace to a user and none else (ofcourse >other than root) can see the data. > >For more info, have a look at: >http://www.mysql.com/doc/en/InnoDB_File_space.html > >Enjoy >Nitin > > >----- Original Message ----- >From: "Leo Huang" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Tuesday, November 04, 2003 8:00 PM >Subject: Re: InnoDB Questions > > > > >>-----BEGIN PGP SIGNED MESSAGE----- >>Hash: SHA1 >> >>Thank you very much for your reply, Nitin. >> >>I did read the Adding_and_removing in the manual, but it says >>"Currently you cannot remove a datafile from InnoDB. To decrease the >>size of your database you have to use `mysqldump' to dump all your >>tables, create a new database, and import your tables to the new >>database." >>It does reduce the size of the database(similar to optimize for >>MyISAM), but it doesn't reduce the size of the file. >> >>Could you explain a bit what is a "tablespace"? >> >>Thanks, >>Leo >> >>/etc/my.cnf: >>[mysqld] >>datadir=/var/lib/mysql >>set-variable=max_connections=300 >>innodb_data_file_path = ibdata1:10M:autoextend >>default-table-type=InnoDB >>set-variable = innodb_buffer_pool_size=512M >>set-variable = innodb_additional_mem_pool_size=10M >>set-variable = innodb_log_file_size=10M >>set-variable = innodb_log_files_in_group=3 >>set-variable = innodb_log_buffer_size=8M >>innodb_flush_log_at_trx_commit=1 >> >> >>[mysql.server] >>user=mysql >>basedir=/usr >> >>[safe_mysqld] >>err-log=/var/log/mysqld.log >>pid-file=/var/run/mysqld/mysqld.pid >> >> >> >>- ----- Original Message ----- >>From: "Nitin" <[EMAIL PROTECTED]> >>To: "Leo Huang" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> >>Sent: Wednesday, November 05, 2003 1:01 AM >>Subject: Re: InnoDB Questions >> >> >> >> >>>Hello, >>> >>>first things first, you cann't resize your datafiles without >>> >>> >>shutting down >> >> >>>your database. if it's ok with you, have a look at >>>http://www.mysql.com/doc/en/Adding_and_removing.html >>> >>>you may want to have a look at you my.cnf file, stored in mysql data >>> >>> >>dir or >> >> >>>in /etc dir, for the default options specified there fo the datafile >>> >>> >>with: >> >> >>>innodb_data_file_path >>> >>>Yes, you can add data file, just add another entry to above option. >>> >>> >>option >> >> >>>entry is self-explainatory. >>> >>>At last, ibdata1, ibdata2.... are actual data files used to store >>> >>> >>actual >> >> >>>data. one or more of these files are attached to one tablespace and >>> >>> >>one file >> >> >>>cant span across tablespaces. >>> >>>ib_logfile0, ib_logfile1 are log files, which are used to log sql >>> >>> >>statements >> >> >>>applied to database. these files are used to restore data in case of >>> >>> >>any >> >> >>>crash or mishap. >>> >>>for further info, have a look at: >>>http://www.mysql.com/doc/en/InnoDB_start.html >>> >>>Enjoy >>>Nitin >>> >>> >>>----- Original Message ----- >>>From: "Leo Huang" <[EMAIL PROTECTED]> >>>To: <[EMAIL PROTECTED]> >>>Sent: Tuesday, November 04, 2003 6:28 PM >>>Subject: InnoDB Questions >>> >>> >>> >>> >>>>Hello, >>>> >>>>I have a few questions about InnoDB. >>>>I am new to InnoDB, and just converted my MyISAM tables into >>>> >>>> >>InnoDB a >> >> >>>>few days ago. >>>>I notice that it generates these files >>>> >>>>-rw-rw---- 1 mysql mysql 2560 Nov 2 13:07 >>>>ib_arch_log_0000000000 >>>>-rw-rw---- 1 mysql mysql 790626304 Nov 4 20:44 ibdata1 >>>>-rw-rw---- 1 mysql mysql 10485760 Nov 4 20:44 >>>> >>>> >>ib_logfile0 >> >> >>>>-rw-rw---- 1 mysql mysql 10485760 Nov 4 20:44 >>>> >>>> >>ib_logfile1 >> >> >>>>-rw-rw---- 1 mysql mysql 10485760 Nov 3 00:02 >>>> >>>> >>ib_logfile2 >> >> >>>>But how can those files work together? >>>> >>>>In InnoDB documentation, it suggests to add another file ibdata2 >>>> >>>> >>to >> >> >>>>get higher performance. Can I do that now, after I have created >>>> >>>> >>the >> >> >>>>ibdata1 and used it for a while? >>>> >>>>The most important thing is I deleted a 300M database, but the >>>> >>>> >>ibdata1 >> >> >>>>remains the same size. MyPHPAdmin says 330,000KB free. How can I >>>> >>>> >>make >> >> >>>>the data file smaller? >>>> >>>>I will be really appreciated if someone can briefly describe >>>> >>>> >>what's >> >> >>>>happening to those files or point me to some articles. >>>> >>>>Thanks a lot, >>>>Leo >>>> >>>> >>>>-- >>>>MySQL General Mailing List >>>>For list archives: http://lists.mysql.com/mysql >>>>To unsubscribe: >>>> >>>> >>>http://lists.mysql.com/[EMAIL PROTECTED] >>> >>> >>> >>> >>> >>-----BEGIN PGP SIGNATURE----- >>Version: PGP 8.0.2 >> >>iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN >>h21IQZ8ozOUeELhvWSpznyTI >>=H/2E >>-----END PGP SIGNATURE----- >> >> >>-- >>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]