Using SuiteCRM 7.0.2 in a VM.
Have joined a new company and want to take the SuiteCRM VM to this company. But I need to clear out all the data and start fresh.
Is there a script or method to clear out the database including all the marked deleted but still stored data?
I would move the VM the way it is. Once installed:
Use phpMyAdmin to DROP all of the tables
Go into config.php and edit ‘installer_locked’ => true, to false (line 292… and save file)
Run SuiteCRM and the installer will create new, clean, tables.
Thanks dev77.
Can you confirm that only the data will be removed (ie. new clean tables created)?
I’ve set the php.ini, config.php, logs, currency etc. etc. and would prefer not to redo all that. … Assuming I remember how I did it all in the first place.
If you do a DROP on all the tables, they are erased. Gone.
Suite will create NEW empty tables when your run Suite the first time (and the installer flag is set in config.php)
A lot of people do a TRUNCATE of tables. That will empty them, but it will also empty some of the tables that Suite needs to run (i.e. the admin data.) You don’t want to do that.
When you come down to it you could just install the VM on the new server or desktop, delete the Suite directory, DROP all the tables and just start from scratch, but it isn’t necessary to copy over all the Suite files since you have them.
Before you do all of this, after you install the VM make sure that Suite runs with the current data before your drop the tables! If it does not run, find out why before you get rid of the data.
One of the great omissions of the Sugar architecture is that you can’t easily re-install Sugar with an existing database. You literally have to re-isntall and let it create a new database, then you have to drop all the tables and import them from the back-up sql “dump” file that you better have!
It’s not that hard, but each year a few people are happy to pay me $250 to SQLdump (i.e. backup) their old data and re-install Sugar for them when they move to a new server. (Well, maybe “happy” isn’t the right word!! No, I’m not soliciting business… I’m a web designer, not a SugarCRM guru… I just do this a few of times a year when asked. We use Sugar to keep track of our victims (oops, I mean clients!) and projects, etc. Works well for us over the years.)
Thanks again, dev77.
I’ll keep the link provided in mind in case we need some devo work.
I dropped the database, changed the config.php and re-ran the install. So far so good.
Looks like there are some items that I will need to set-up anyway. But it looks like those are related to the SuiteCRM rather than the underlying php etc. Thank goodness.
Now to figure out why I get the “Connection for controluser as defined in your configuration failed.” in phpmyadmin. I’ve followed multiple directions for Debian Wheezy but can’t get this error to go away.
Thanks again.
That’s a phpMyAdmin issue. Quite common. You will find the answer somewhere from here:
Here is what I have started using for MSSQL.
Declare @table nvarchar(128),
@table_custom nvarchar(128),
@sql nvarchar(max)
Declare Table_Cursor cursor for
select distinct ‘[’+table_schema + ‘].[’+table_name+’]’,’[’+table_schema + ‘].[’+table_name+’_cstm]’
from INFORMATION_SCHEMA.Columns
where column_name = ‘deleted’
order by ‘[’+table_schema + ‘].[’+table_name+’]’,’[’+table_schema + ‘].[’+table_name+’_cstm]’
Open Table_Cursor
Fetch Next from Table_Cursor into @table,@table_custom
While @@FETCH_STATUS = 0
Begin
--clear any entries in the custom table
If(select count(*) from information_schema.columns where '['+table_schema + '].['+table_name+']' = @table_custom) > 0
Begin
Set @sql = ' Truncate table '+ @table_custom
execute(@sql)
End
--clear regular table
If(select count(*) from information_schema.columns where '['+table_schema + '].['+table_name+']' = @table) > 0
Begin
--test for user table to exclude admin users
If @table = '[dbo].[users]' set @sql = ' delete '+@table +' where is_admin = 0'
else set @sql = ' Truncate table '+@table
execute(@sql)
End
Fetch Next from Table_Cursor into @table,@table_custom
End
close Table_Cursor
deallocate Table_Cursor