Inserting data into MySQL from Arduino

Projects Networking, Protocols, and Devices
1

Hi everybody,
I need your help on the following setup:
On the arduino side, I have an UNO with an ethershield and the MySQL Connector/Arduino from MySQL Connector/Arduino in Launchpad
I have striped down the code for simplification and I try to connect and insert just two rows of data into the database

#include <SPI.h>
#include <Ethernet.h>
#include <sha1.h>
#include <avr/pgmspace.h>
#include <stdlib.h>
#include <mysql.h>

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,54);
char user[] = "admin";
char password[] = "abcd";

Connector my_conn;        // The Connector/Arduino reference

const char INSERT_TEXT[] = "INSERT INTO joomladb.meteo VALUES ('Hello, MySQL!', NULL)";
const char INSERT_DATA[] = "INSERT INTO joomladb.meteo VALUES (%s, NULL)";

void setup() {
  Ethernet.begin(mac_addr);
  Serial.begin(9600);
  
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");

 
  //
  // INSERT Examples
  //

  my_conn.cmd_query(INSERT_TEXT);
  // Now, let's check our results.
 
 
  char query[64];
  char temperature[10];
  dtostrf(value_read, 1, 1, temperature);
  sprintf(query, INSERT_DATA, temperature);
  my_conn.cmd_query(query);
 
}

void loop() {
}

On the PC side I have installed XAMPP distribution setup with the MySQL, PHPAdmin and Joomla.
I haven't done anything special here, other than adding the user=admin with the password mentioned above with PHP admin into MySQL.

When running the UNO it stacks in "Connecting..." and no records are added into MySQL. Looking in MySQL log (from XAMPP control panel) I see that it finds the data base but it has not presented all the qualification right to connect.

I think that something is wrong on MySQL side and PHPadmin and I am novice to this. Can somebody help me? HAs anyone done it with this XAMPP distribution before? Any setup suggestions are welcome. Thanks.

2

What exactly does your xampp log file say?

Have you allowed the admin user access to the database in use from that IP address?

Does mysql have networking enabled even (on some xampp installs it isn't)?

3

A common problem is not opening tcp port 3306 in the server firewall. Did you do that?

4

WendoNZ,
For the user admin I have chanced the host to % to allow all accesses, not just the localhost which was the default. Also in my.ini the line #skip-networking is commented so i guess is network enabled.

This is the log:

141101 13:38:58 [Note] Plugin 'FEDERATED' is disabled.
141101 13:38:58 InnoDB: The InnoDB memory heap is disabled
141101 13:38:58 InnoDB: Mutexes and rw_locks use Windows interlocked functions
141101 13:38:58 InnoDB: Compressed tables use zlib 1.2.3
141101 13:38:58 InnoDB: Initializing buffer pool, size = 16.0M
141101 13:38:59 InnoDB: Completed initialization of buffer pool
141101 13:38:59 InnoDB: highest supported file format is Barracuda.
141101 13:39:00 InnoDB: Waiting for the background threads to start
141101 13:39:02 InnoDB: 5.5.39 started; log sequence number 2885232
141101 13:39:02 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141101 13:39:02 [Note] - '0.0.0.0' resolves to '0.0.0.0';
141101 13:39:02 [Note] Server socket created on IP: '0.0.0.0'.
141101 13:39:02 [Warning] Found invalid password for user: 'mike@localhost'; Ignoring user
141101 13:39:02 [Warning] Found invalid password for user: 'admin@%'; Ignoring user
141101 13:39:03 [Note] Event Scheduler: Loaded 0 events
141101 13:39:03 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.39' socket: '' port: 3306 MySQL Community Server (GPL)

The warnings have come up after I changed the password in phpmyadmin for these users.
I guess now I can't use these two users from UNO.

SurferTim,
The port 3306 is open from firewall

5

I guess now I can't use these two users from UNO.

I'd say that that was a near certainty.

6

OK! The problem was solved. There were two issues and both were from the PC side:

  1. I had inserted users/passwords from the mySQL db/USER table and passwords were plain texts not encrypted. I should have inserted users from home/Top/ Users menu. All these were done from phpMyAdmin

  2. Users had to have host field = % to accept bindings from network and not from localhost only.

At last I can move on. Thanks everybody! :smiley:

7

I have also problem with xamp and msql. Seems that there is some kind of connection problem? I have opened right port + and made a database. Serial printing: "Connecting..." and nothing more.

Strange is that it's not moving then...

Example needs mysql connector download from arduino libraries.

/*
  MySQL Connector/Arduino Example : complex insert

  This example demonstrates how to issue an INSERT query to store data in a
  table using data from variables in our sketch. In this case, we supply the
  values dynamically.

  This sketch simulates storing data from a sensor in a table.

  For this, we will create a special database and table for testing.
  The following are the SQL commands you will need to run in order to setup
  your database for running this sketch.

  CREATE DATABASE test_arduino;
  CREATE TABLE test_arduino.hello_sensor (
    num integer primary key auto_increment,
    message char(40),
    sensor_num integer,
    value float,
    recorded timestamp
  );

  Here we have a table that contains an auto_increment primary key, a text
  field, a field to identify the sensor, the value read, and timestamp of
  the recorded data.

  Note: Since this sketch uses test data, we place the INSERT in the setup()
        method so that it runs only once. Typically, you would have the
        INSERT in the loop() method after your code to read from the sensor.

  INSTRUCTIONS FOR USE

  1) Create the database and table as shown above.
  2) Change the address of the server to the IP address of the MySQL server
  3) Change the user and password to a valid MySQL user and password
  4) Connect a USB cable to your Arduino
  5) Select the correct board and port
  6) Compile and upload the sketch to your Arduino
  7) Once uploaded, open Serial Monitor (use 115200 speed) and observe
  8) After the sketch has run for some time, open a mysql client and issue
     the command: "SELECT * FROM test_arduino.hello_sensor" to see the data
     recorded. Note the field values and how the database handles both the
     auto_increment and timestamp fields for us. You can clear the data with
     "DELETE FROM test_arduino.hello_sensor".

  Note: The MAC address can be anything so long as it is unique on your network.

  Created by: Dr. Charles A. Bell
*/
#include <SPI.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(192,168,100,48);  // IP of the MySQL *server* here
char user[] = "root";              // MySQL user login username
char password[] = "";        // MySQL user login password

// Sample query
char INSERT_DATA[] = "INSERT INTO test_arduino.hello_sensor (message, sensor_num, value) VALUES ('%s',%d,%s)";
char query[128];
char temperature[10];

EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
    // Initiate the query class instance
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Save
    dtostrf(50.125, 1, 1, temperature);
    sprintf(query, INSERT_DATA, "test sensor", 24, temperature);
    // Execute the query
    cur_mem->execute(query);
    // Note: since there are no results, we do not need to read any data
    // Deleting the cursor also frees up memory used
    delete cur_mem;
    Serial.println("Data recorded.");
  }
  else
    Serial.println("Connection failed.");
  conn.close();
}


void loop() {
}
8

There is specified max connection times 3. But seems that it somehow stop working when it trying to connect to xamp-database... (like staying infinite loop).