Hi, >From my experience, once you have created a datafile it doesn't work when you try to make it larger. If you dont care about the data thats already in it then delete the current ibdata files and restart mysql. If you do then put the size back to how it was, dump the data, shutdown the server, change the cfg file and remove the ibdata file. At startup it should create the new larger file, then you can import the dumped data.
Marvin. -----Original Message----- From: Leo Huang [mailto:[EMAIL PROTECTED] Sent: 05 November 2003 07:40 To: Nitin Cc: [EMAIL PROTECTED] Subject: Re: InnoDB Questions 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] ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
