Inserting data into MySQL from Arduino

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

Inserting data into MySQL from Arduino

Postby thermike » 30. October 2014 17:41

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 https://launchpad.net/mysql-arduino
I have striped down the code for simplification and I try to connect and insert just two rows of data into the database

On the PC side I have installed XAMPP distribution setup with the MySQL, PHPAdmin, Apache and also 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 Arduino 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 PHPMyAdmin 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.
thermike
 
Posts: 4
Joined: 30. October 2014 17:35
Operating System: WINDOWS XP

Re: Inserting data into MySQL from Arduino

Postby Nobbie » 30. October 2014 22:44

thermike wrote:I think that something is wrong on MySQL side and PHPMyAdmin and I am novice to this.


No, definately not. There is something wrong with this "Arduino", but i cannot help on that, because i have no idea, what it is. There is something wrong in Arduino configuration in order to connect to MySQL properly or similar.
Nobbie
 
Posts: 8779
Joined: 09. March 2008 13:04

Re: Inserting data into MySQL from Arduino

Postby JJ_Tagy » 31. October 2014 02:11

I'm thinking permissions for user using external connection to MySQL.
JJ_Tagy
 
Posts: 678
Joined: 30. January 2012 13:44
Operating System: Windows 10 Pro x64

Re: Inserting data into MySQL from Arduino

Postby thermike » 31. October 2014 22:53

Thanks JJ_Tagy,
I kept the arduino side constant and changed from phpMyAdmin the host for admin user to %. Is this a way to tell that admin can be user from inside and outside? Please verify.
On mysql_error.log I get the following:

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

and as I said without changing anything on arduino code now I get Packet error...Error 255.
Am I at the right track?
thermike
 
Posts: 4
Joined: 30. October 2014 17:35
Operating System: WINDOWS XP

Re: Inserting data into MySQL from Arduino

Postby JJ_Tagy » 01. November 2014 01:11

Yes, % means all. Just beware, you open yourself up for probing if you expose your system without specifying a specific whitelist IP. Looking at that log, it looks like your script is using the wrong password or the user doesn't exist in MySQL.
JJ_Tagy
 
Posts: 678
Joined: 30. January 2012 13:44
Operating System: Windows 10 Pro x64

Re: Inserting data into MySQL from Arduino

Postby thermike » 01. November 2014 09:19

JJ_Tagy,
OK thanks, I will restrict the list of IP later.

The only thing I did on phpMyAdmin is to change the passwords on mike and admin user. There is something I miss here with the password change management in mySQL. Is there a compatibilty issue in various versions of mySQL? And I don't understand this hashing of passwords mysql does. At the beginning passwords are scrabled and when you change them and they become visible (in phpadmin) the log shows this message about " [Warning] Found invalid password for user: 'admin@%'; Ignoring user".

Nobbie,
For the time being i keep the arduino side constant to see how the mysql part is behaving.
thermike
 
Posts: 4
Joined: 30. October 2014 17:35
Operating System: WINDOWS XP

Re: Inserting data into MySQL from Arduino

Postby Nobbie » 01. November 2014 11:53

These error messages look very strange:

141031 23:41:48 [Warning] Found invalid password for user: 'mike@localhost'; Ignoring user
141031 23:41:48 [Warning] Found invalid password for user: 'admin@%'; Ignoring user


This does NOT mean, that any client tries to connect with the wrong password, instead this means, that the password is corrupted. Usually passwords are stored in a crypted manner and it looks like this encryption has been overwritten by "something", so that it is not a valid password. I dont know if you are going to use these MySQL UserIDs, but there is something very strange happening, it just looks like as someone edited the MySQL User Table manually and inserts invalid passwords (or similar). I have never seen this message before. Find out, what is going wrong there!

P.S.:
The only thing I did on phpMyAdmin is to change the passwords on mike and admin user. There is something I miss here with the password change management in mySQL


Aha - i did not read that before, but obviously, you did not manage to do it the right way. You MUST NOT change the User table manually, instead you *should* use phpmyadmin to edit the Users with the appropriate dialog. I think, that you provided plain text passwords, but passwords usually are encrypted using the MySQL Builtin Function "PASSWORD()". Therefore you corrupted these two users.

You have to choose "Users" from the top menu, activate the user "admin" for example, submit and choose "Change Password". In that dialog you will find an option, if the Password should be compatible to MySQL 4.0 (and prior) or to MySQL 4.1 (and later). Activate the correct option, then appy the password. DO NOT EDIT USER TABLES MANUALLY!!
Nobbie
 
Posts: 8779
Joined: 09. March 2008 13:04

Re: Inserting data into MySQL from Arduino

Postby thermike » 01. November 2014 16:24

Yes! That's it!

To wrap up there were two issues:

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.

Thanks! :D
thermike
 
Posts: 4
Joined: 30. October 2014 17:35
Operating System: WINDOWS XP


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 1 guest