Ethernet Board - does anyone have it running for one week or more ?

I have been working on having a Arduino Ethernet board up running with a program that send a SQL command to my homepage. But the program have never been running for more that 24 hours before it freezes. The programs function is

  1. send a new value to the SQL database.
  2. wait 6 minutes and blink with the LED on the board in the meanwhile
  3. loop to 1)

A) I have read about other users broblems with using variables of the type "String" and because of that, I don't use Strings in my program.
B) After normaly 6-16 hours my program stop blinking the on board LED and the SQL database is not updated.
C) I am printing the free RAM value to the serial port and the amount of free RAM don't change.
D) I am using the Serial.println(F())) when writing text to the serial port.

I have tried a lot of different programming, but the Arduino Ethernet freezes all the time after running 6-16 hours. I have googled for weeks/month about this problem and found, that a lot of programmers are having problems with this board. BUT DOES ANYONE HAVE A ARDUINO ETHERNET BOARD UP RUNNING WITH ETHERNET/SQL FUNCTIONALITY FOR WEEKS WITHOUT RESETTING THE BOARD ? AND ARE YOU WILLING TO SHARE YOUR PROGRAM FOR TESTING ?

I hope someone will help because I otherwise have to change to an other solution like RaspberryPi.

I did 12 days with a Mega2560 and ethernet shield without a fail. I had to stop the test because I needed my Mega for another project test, not because it failed or locked up. I used the GET version of this code:
http://playground.arduino.cc/Code/WebClient

I did get at least one "Timeout" message, but that doesn't lock up my code. It keeps right on going.

I used a derived version of that code to grab temperature data from an xBee and push it to a SQL database via a GET request. It ran for over a month in test before I sent it off to the end user.

Maybe you would look at my code for 5 minutes ? Please be aware of, at I am not expert in coading :slight_smile:
I have simplified my code and out-commented some of it. My password and homepage is exchanged with xxxxx.

My CODE:

/** WEATHERSTATION
*/
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"
#include "EthernetUdp.h"

/* General parameters /
int hh=1; /
hour number of current time /
int T; /
Temperature /
int P; /
Pressure /
int RH; /
Relative Humidity /
int debug; /
Set to 1 if debug information is needed */
int i; /*counter */
char CommandChar[]="";
unsigned int localPort = 8888;
IPAddress timeServer(94, 231, 110, 37); // dk.pool.ntp.org NTP server
int led = 9;
int Midlertidig=1;

/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

/* Setup for the Connector/Arduino /
Connector my_conn; /
The Connector/Arduino reference */

/* SQL database information /
IPAddress server(xxx,xxx,xxx,xxx);
char user[] = "xxxxxxxxxx";
char password[] = "xxxxxxxxx";
char DeleteOne_SQL[] = ""; /
Sletter et datapunkt /
char DeleteAll_SQL[] = ""; /
Sletter alle datapunkter /
char Update_SQL[] = ""; /
Opdaterer/ændrer et datapunkt */

const int NTP_PACKET_SIZE= 48; // NTP time stamp is in the first 48 bytes of the message
byte packetBuffer[ NTP_PACKET_SIZE];
EthernetUDP Udp;

void setup() {
pinMode(led, OUTPUT);

Ethernet.begin(mac_addr);
Serial.begin(115200);
delay(1000);
Udp.begin(localPort);
Serial.println(F("Connecting database."));
if (my_conn.mysql_connect(server, 3306, user, password))
{
Serial.print(F("Try to send SQL command: "));
delay(500);
/* Update SQL data */
my_conn.cmd_query("UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxx SET temp=0 WHERE tid=0"); delay(500);
Serial.println(F("Query Success!"));
delay(500);
my_conn.disconnect();
delay(500);
}
else
Serial.println(F("Connection failed."));

}

int freeRam () {
extern int __heap_start, *__brkval;
int v;
return (int) &v - (__brkval == 0 ? (int) &__heap_start : (int) __brkval);
}

// send an NTP request to the time server at the given address
unsigned long sendNTPpacket(IPAddress& address)
{
// set all bytes in the buffer to 0
memset(packetBuffer, 0, NTP_PACKET_SIZE);
// Initialize values needed to form NTP request
// (see URL above for details on the packets)
packetBuffer[0] = 0b11100011; // LI, Version, Mode
packetBuffer[1] = 0; // Stratum, or type of clock
packetBuffer[2] = 6; // Polling Interval
packetBuffer[3] = 0xEC; // Peer Clock Precision
// 8 bytes of zero for Root Delay & Root Dispersion
packetBuffer[12] = 49;
packetBuffer[13] = 0x4E;
packetBuffer[14] = 49;
packetBuffer[15] = 52;

// all NTP fields have been given values, now
// you can send a packet requesting a timestamp:
Udp.beginPacket(address, 123); //NTP requests are to port 123
Udp.write(packetBuffer,NTP_PACKET_SIZE);
Udp.endPacket();
}

void GetTime() {
/* Midlertidig - venter en given tid /
for (int i=0; i <= 10
60; i++){
digitalWrite(led, HIGH); // turn the LED on (HIGH is the voltage level)
delay(240); // wait for a second
digitalWrite(led, LOW); // turn the LED off by making the voltage LOW
delay(240);
digitalWrite(led, HIGH); // turn the LED on (HIGH is the voltage level)
delay(240); // wait for a second
digitalWrite(led, LOW); // turn the LED off by making the voltage LOW
delay(240);
Serial.print(i);
Serial.print(F("/600 "));
Serial.print(F("Free SRAM="));
Serial.println(freeRam ());
}

/*Beregner tiden */
sendNTPpacket(timeServer); // send an NTP packet to a time server
delay(1000);
if ( Udp.parsePacket() ) {
// We've received a packet, read the data from it
Udp.read(packetBuffer,NTP_PACKET_SIZE);
unsigned long highWord = word(packetBuffer[40], packetBuffer[41]);
unsigned long lowWord = word(packetBuffer[42], packetBuffer[43]);
unsigned long secsSince1900 = highWord << 16 | lowWord;
const unsigned long seventyYears = 2208988800UL;
unsigned long epoch = secsSince1900 - seventyYears;
hh=(epoch % 86400L) / 3600+2; // print the hour (86400 equals secs per day)
}
if (hh==24) hh=0;
if (hh==25) hh=1;
if (hh==26) hh=2;
}

void GetWeatherData() {
float sensorValue = analogRead(A0);
sensorValue = sensorValue/10235;
sensorValue=10000
sensorValue/(5-sensorValue); /* Rx */
sensorValue=1/(log(sensorValue/10000)/3950+1/298.15)-273.15;
T = sensorValue;
Serial.print(F("T="));
Serial.println(T);
}

void DeleteAllSQLData() {}

void InsertSQLData() {
/* String Dummy1 = "";
String Dummy2 = "UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxxx SET temp=";
String Dummy3 = " WHERE tid=";
char charBuf[80];
/
/
Prepare SQL string /
/
Dummy1=Dummy2;
Dummy1 +=T;
Dummy1 += Dummy3;
Dummy1 += hh;
Dummy1.toCharArray(charBuf,80);
/
/
Connect to SQL and send string if connected /
if (my_conn.mysql_connect(server, 3306, user, password))
{
Serial.print(F("Try to send SQL command: "));
delay(1000);
if (Midlertidig==5) {
my_conn.cmd_query("UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxxx SET temp=5 WHERE tid=3");
Midlertidig=1;
}
if (Midlertidig==4) {
my_conn.cmd_query("UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxxx SET temp=4 WHERE tid=3");
Midlertidig=5;
}
if (Midlertidig==3) {
my_conn.cmd_query("UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxxx SET temp=3 WHERE tid=3");
Midlertidig=4;
}
if (Midlertidig==2) {
my_conn.cmd_query("UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxxx SET temp=2 WHERE tid=3");
Midlertidig=3;
}
if (Midlertidig==1) {
my_conn.cmd_query("UPDATE xxxxxxxxxxxxxx.xxxxxxxxxxxx SET temp=1 WHERE tid=3");
Midlertidig=2;
}
/
my_conn.cmd_query(charBuf); */
delay(1000);
Serial.println(F("Query Success!"));
my_conn.disconnect();
}
else
{
Serial.println(F("Connection failed."));
}
}

void ShowDataInDisplay() {
}

void loop() {
debug=1;
hh=0;
while (1) {
/* Hent vejr data til databasen */
GetWeatherData();

/* Gemmer data i databasen */
InsertSQLData();

/* Forudsætning: database med tid 0 - 23 i første søjle og data i de andre søjler /
/
Vent en time og hent tid */
GetTime();
}
}

It would certainly be a lot simpler to create a few PHP scripts on the server that MySQL is running on, and stop wasting memory on the sql library.

Isn't the your router's dhcp an issue? I'd give the arduino server a static ip.

Great idear, but in this case I prefer to have all functionality on the Arduino.

mistergreen - thanks for your input. Is it enough to use the following code with my addresses or do I need more:

IPAddress ip(192,168,2,2);
IPAddress gateway(192, 168, 2, 1);
IPAddress subnet(255, 255, 255, 0);

You'd have to set your router setting to get the arduino that static ip. You'd need the arduino's MAC address.

I actually came here looking for tips on SQL comms, but I have had this ethernet instability issue before and I think I found a solution (and maybe the cause) - the SD card. (I have an EtherMega - Mega2560 with the ethernet shield built in, functionally the same)

In every sketch where I plan to use ethernet, I also initialise the SD card - since I started doing this I haven't had any issues with lockups.

(Update #2: Forget the SD library and just set the SD CS pin (4 on my mega) to output and drive it high (CS is active-low))

Just include the SD library

#include <SD.h> //Default SD library

and initialise the SD card in setup()

if (!SD.begin(4)) {
// initialize SD to stop interference with ethernet.
// Don't care if it fails, we're not using SD anyway
}

This code is from a project which has successfully run for days straight while spamming my local network with UDP packets (every 250mS or so). The reason I stumbled across this was I tried the tinywebserver example, and it worked fine reading from the SD and using ethernet, but when I tried using ethernet in another project the board would lock up after a few seconds of heavy traffic, lasting longer in light traffic but still no more than a few minutes. Figuring that the only difference was the lack of SD use, and the SD shares some data lines with the ethernet chip, I added the above code and it started working fine. (I had left the SD card in, haven't tried with it out)

I'll be curious to see if this helps anyone else.
(I'm trying my current project without the above code, hoping to get it to crash but it hasn't yet. Really hope I don't prove myself wrong.)
Update 1: Success! I had to jiggle the SD card around a bit to get the SD example sketches to work, then when I uploaded my current sketch (remote I/O via UDP) it would crash within the first 5 seconds. Uncommented the SD initialisation and it's working fine again. I suspect the SD chip select line may be floating, which activates the SD card and interferes with the W5100, as they share MISO, MOSI and SCK.

OK, if my last post was tl;dr then try this at the start of your setup (assuming you're not using the SD card):

void setup()
{
pinMode(4, OUTPUT); //SD card CS pin
digitalWrite(4, HIGH); //Disable SD card

STATUS:
I made some serial printing and found that the code stop at my line "if (my_conn.mysql_connect(server, 3306, user, password))". When I ping the Arduino Ethernet it replies.
I tried with power from the USB on my PC end extra cooling on the chip, but it don't help.

NOW:
I will test the SD card setup mentioned by "drake600".

GOAL:
To upload a functional code that others can use because, a lot of programmers are having problems with the SQL/Ethernet functionality.

Check you socket status. You may be running out of sockets. Add this function to your code and call it just before the
"if (my_conn.mysql_connect(server, 3306, user, password))" call.

#include <utility/w5100.h>

byte socketStat[MAX_SOCK_NUM];

void ShowSockStatus()
{
  for (int i = 0; i < MAX_SOCK_NUM; i++) {
    Serial.print(F("Socket#"));
    Serial.print(i);
    uint8_t s = W5100.readSnSR(i);
    socketStat[i] = s;
    Serial.print(F(":0x"));
    Serial.print(s,16);
    Serial.print(F(" "));
    Serial.print(W5100.readSnPORT(i));
    Serial.print(F(" D:"));
    uint8_t dip[4];
    W5100.readSnDIPR(i, dip);
    for (int j=0; j<4; j++) {
      Serial.print(dip[j],10);
      if (j<3) Serial.print(".");
    }
    Serial.print(F("("));
    Serial.print(W5100.readSnDPORT(i));
    Serial.println(F(")"));
  }
}

A socket status list:
0X0 = available.
0x14 = socket waiting for a connection
0x17 = socket connected to a server.
0x22 = UDP socket.

If that is not it, you may be locking up in your response read if the connection breaks or the server stalls, but I don't see that code in your sketch. Take a look at the link I posted above to see a timeout function.

SurferTim - thanks for your input. I am testing the Ethernet board with the SD setup as mentioned and it have been running for 7 hours now. If it runs for 24 hours I have found the solutions and if not, I will test with your suggested code.
I will return with a status when the 24h is reached or the board freezes.

If you have a SD card in the shield's slot, then that may correct your problem. However, if your code does not have a timeout feature, then you are living on borrowed time.

I have a SD card in the BOARD'S slot.

What do you mean by "timeout feature" in this case ?

This is the timeout feature in my code. If the connection breaks or the server stalls, this will close the connection from the client end and prevent an endless loop. It closes the connection if no packet is received for 10 seconds.

// connectLoop controls the hardware fail timeout
  int connectLoop = 0;

  while(client.connected())
  {
    while(client.available())
    {
      inChar = client.read();
      Serial.write(inChar);
      // set connectLoop to zero if a packet arrives
      connectLoop = 0;
    }

    connectLoop++;

    // if more than 10000 milliseconds since the last packet
    if(connectLoop > 10000)
    {
      // then close the connection from this end.
      Serial.println();
      Serial.println(F("Timeout"));
      client.stop();
    }
    // this is a delay for the connectLoop timing
    delay(1);
  }

  Serial.println();

  Serial.println(F("disconnecting."));
  // close client end
  client.stop();

Japping - I tried your code out, but I couldn't get it to connect to my mySQL server Now that I disabled windows firewall :blush: and got the demo from Chuck's Blog working (I'm guessing this is where you started from) I'll have another go at your code and see how it goes. The demo is currently filling my test DB with a new record every second, no problems so far.

Update: Japping - your sketch is running fine, I removed all the NTP code so it just runs a single INSERT INTO query instead of UPDATE, and I removed all the delays (and the one in mysql.cpp:85 is now "if (i >= 0) delay(1000);" so it will only delay the second attempt) so it's inserting rows at about 1 every second. I'll leave it running for a while and see if it falls over, but it's doing OK so far. (I am driving pin 4 high to keep the SD card disabled, just at the start of setup)

45 minutes: 3100 rows in my test db and counting, thanks for the kickstart into SQL/Arduino, I hope the pin 4 thing helps!

Yes, My code works fine after disabling the SD card. Within a few days, I will upload a simple program including information about setting up the MySQL table. I hope it will help others !

Thanks a lot for all your inputs, that I also will test afterwards.