How to connect mariadb and access the tables

Frappe Framework
1

hiii sir, we installed ubuntu 18.04 as virtual machine in server. and installed frappe framework an all . putty also installed.now we are trying to acess mariadb. can u explain me how to connect mariadb and access the tables???

2

either

cd frappe-bench
bench mariadb

or

mysql -u root -p -h localhost

the second command is more general, so you’ll have to choose your frappe database yet (`USE [frappe_db_name], while with the first command you are inside of the current frappe database automatically.

5 Likes
3

thank you sir, how to show the tables in maria db?

4

First open the mariaDB console with:

bench --site sitename mariadb

You may list the tables with:

SHOW TABLES;

SHOW TABLES;
+---------------------------------------------------+
| Tables_in_3ac8289bc6f4835e                        |
+---------------------------------------------------+
| __Auth                                            |
| __UserSettings                                    |
| __global_search                                   |
| tabAbout Us Team Member                           |
| tabAcademic Term                                  |
| tabAcademic Year                                  |
| tabAccount                                        |
| tabAccounting Period                              |
| tabActivity Cost                                  |
| tabActivity Log                                   |
| tabActivity Type                                  |
| tabAdditional Salary                              |
| tabAddress                                        |
| tabAddress Template                               |
| tabAgriculture Analysis Criteria                  |
| tabAgriculture Task                               |
| tabAllowed To Transact With                       |
| tabAntibiotic                                     |
| tabAppointment Type                               |
| tabAppraisal                                      |
| tabAppraisal Goal                                 |
| tabAppraisal Template                             |
| tabAppraisal Template Goal                        |
| tabAssessment Criteria                            |
| tabAssessment Criteria Group                      |
| tabAssessment Group                               |
| tabAssessment Plan                                |
| tabAssessment Plan Criteria                       |
| tabAssessment Result                              |
| tabAssessment Result Detail                       |
| tabAsset

If we want to list all the item in the Item DocType list:

SELECT * FROM tabItem;

We will get:

MariaDB [3ac8289bc6f4835e]> SELECT * FROM tabItem;
+------+----------------------------+----------------------------+---------------+---------------+-----------+--------+-------------+------------+-----+------------+---------------+----
| name | creation                   | modified                   | modified_by   | owner         | docstatus | parent | parentfield | parenttype | idx | item_group | is_sales_item | ret
+------+----------------------------+----------------------------+---------------+---------------+-----------+--------+-------------+------------+-----+------------+---------------+----
| 5712 | 2019-02-28 05:10:41.952123 | 2019-02-28 05:10:41.952123 | Administrator | Administrator |         0 | NULL   | NULL        | NULL       |   0 | Consumable |             1 |    
| 5713 | 2019-02-28 05:13:33.680472 | 2019-02-28 05:13:33.680472 | Administrator | Administrator |         0 | NULL   | NULL        | NULL       |   0 | Consumable |             1 |    
+------+----------------------------+----------------------------+---------------+---------------+-----------+--------+-------------+------------+-----+------------+---------------+----
2 rows in set (0.001 sec)
8 Likes
5

thank you so much :slight_smile:

6

A MariaDB Primer - MariaDB Knowledge Base may be worth a read

1 Like
7

If you expect to work frequently with DB install https://dbeaver.io/ on your host machine and connect to DB in VM.
There are additional few steps needed to enable remote access to mariadb. Inside your VM

  1. Open /etc/mysql/my.cnf with nano editor
    nano /etc/mysql/my.cnf
  2. Go to bind-address = 127.0.0.1 beneath the # localhost which is more compatible and is not less secure.
  3. Edit as bind-address = 0.0.0.0
  4. To save this file press Ctrl+o > Yes > Enter
  5. Restart mysql service
    sudo service mysql restart
  6. Sudo mysql -uroot -p
  7. Give db password
  8. GRANT ALL PRIVILEGES ON .(give * before & after .) TO ‘root’@‘%’ identified by ‘frappe’;
  9. quit
2 Likes
8

Thank you so much :slight_smile: