How to delete contents of database

Good day. May I ask how you delete the contents of the Gibbon database? My purpose is to delete the database (or its contents) and then to re-encode and re-import data to Gibbon database during the testing. Below are the steps I followed that will generate error message on number 5. There's still error even if I restart the server after step 3.

1. drop user gibbon@localhost;; <-i forgot if this was my step 1 or step 2
2. drop database gibbon; <-i forgot if this was my step 1 or step 2
3. flush privileges;
4. create database gibbon;
5. create user 'gibbon'@'localhost' identified by 'dbpassword'; <-dbpassword is replaced with the database's password

I forgot to note the error message but it started with "HY00". My installation is Ubuntu Linux 20.04 and mariadb.10.3

Comments

  • Hi Richard, these commands look about right, but you don't need 4 or 5, as Gibbon can do this part during installation. I think the key is to do 2., and I think then 1 and 3 might well be superfluous.

    TBH I usually use a MySQL client (e.g. Sequel Ace or DBeaver) to do this, as it makes life much easier.

    Cheers,

    Ross
  • Hi Ross I have downloaded dbeaver. May I ask what sql commands you enter in dbeaver or sql ace everytime you want to delete Gibbon database contents?

    I tried to only do 'drop database gibbon;' and when I opened [GibbonPath]/installer/install.php this is the message:

    Installation - Step 1
    The directory containing the Gibbon files is not currently writable, or config.php already exists in the root folder and is not empty or is not writable, so the installer cannot proceed.

    I restarted the server but the same error message appears. Below are the next steps I did but the same message still appears:

    1. sudo mariadb
    2. drop database gibbon;
    3. drop user gibbon@localhost;
    4. flush privileges;
    5. exit;
    6. [restart server and view install.php]
    7. sudo mariadb
    8. create database gibbon;
    9. create user 'gibbon'@'localhost' identified by 'replacethiswithdbpassword';
    10. revoke all privileges on *.* from 'gibbon'@'localhost';
    11. grant select, insert, update, delete, create, drop, index, alter, lock tables on gibbon.* to 'gibbon'@'localhost';
    12. flush privileges;
    13. exit
    14. sudo systemctl restart mariadb
    15. sudo systemctl restart apache2
    16. [restart server and view install.php]
    17. revoke all privileges on *.* from 'gibbon'@'localhost';
    18. drop user gibbon@localhost;
    19. drop database gibbon;
    20. flush privileges;
    21. exit
    22. [restart server and view install.php]
    23. sudo systemctl restart mariadb
    24. sudo systemctl restart apache2
    25. view install.php
  • Hi Richard,

    I don't issue any commands directly, but do it using the GUI menu options:



    This is in Sequel Ace, but I'd imagine that DBeaver has a similar way to do it.

    Thanks,

    Ross
  • Hi Ross, thanks for your patience because this topic is outside Gibbon. Please let me know if I understand correctly the steps you do whenever you want to delete your Gibbon data:
    1. Open Sequel Ace and choose Database > Delete Database and choose the database to be deleted
    2. Point browser to [GibbonPath]/installer/install.php, and on the 2nd page will ask for the database details
    3. The 3rd page will display when the database has been created by the Gibbon web app
  • Hi Richard, no problem at all, we are happy to help. Your steps are indeed correct, although for 1. you need to connect Sequel Ace to your MySQL server, and then select the relevant database after the connection is established. Cheers, Ross.
  • Thanks Ross. I will play around with dbeaver. Below are its settings that worked for me (Server: Ubuntu Linux 20.04 and mariadb.10.3; workstation: Windows 10). I had to add them so that the database can be accessed from Windows. But please let me know if these are inappropriate / incorrect settings, or if there are faster ways to go about things. Thank you.

    1. Comment "bind-address" from file /etc/mysql/mariadb.conf/50-server.cnf ; If I remember correctly this was originally set to "localhost" ; But I later set this to the server's IP address and it also seemed to work
    2. Under mariadb, in addition to the existing "create user 'gibbon'@'localhost' identified by 'replacethiswithdbpassword';" , execute additional command "create user 'gibbon'@'%' identified by 'replacethiswithdbpassword';"
    3. Under mariadb, in addition to the existing "grant select, insert, update, delete, create, drop, index, alter, lock tables on gibbon.* to 'gibbon'@'localhost';", execute additional command "grant select, insert, update, delete, create, drop, index, alter, lock tables on gibbon.* to 'gibbon'@'%';"
    4. Under mariadb, execute command "flush privileges;"
  • Hi Richard, these look about right, but do keep in mind that 'gibbon'@'%' allows access from any IP address. For security reasons I'd recommend that you limit this to particular IP addresses. Thanks for sharing the solution that worked for you.
  • Ok thank you Ross for the information.
Sign In or Register to comment.