Routing Between Multiple Subnets

Question

While loading a batch of data into a MySQL database (everything running locally for data management for a research project), the server crashed for no obvious reason. Examination of the logs showed the following errors:

ERROR, InnoDB: Operating system error number 23 in a file operation.
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
ERROR, InnoDB: File .\analysis_lastfm\lastfm_scrobbles.ibd: 'Windows aio' returned 
   OS error 123. Cannot continue operation
InnoDB: Cannot continue operation.

This basically seemed to say that, somehow, the named .ibd file had become corrupted. After various unsuccessful debugging attempts, I finally deleted all my data, reinstalled MySQL, and tried restoring from a backup. This backup was from well before I had had any problems (in other words, it seems doubtful that there's anything wrong with data in the backup). All the other tables restored fine, but after a couple of hours importing data from the lastfm_scrobbles table, the server crashed with the same error.

I realize this question isn't as specific as it could be, but I'm mainly trying to figure out what my next troubleshooting steps should be (keeping in mind I've already reinstalled MySQL and attempted to restore from backup). The only other idea I have at this point is reformatting the hard drive and reattempting the restore, but I don't know if that's really warranted at this point...

Solution

Ok, so you have five networks 10.0.1.0/24, 10.0.2.0/24, 10.0.3.0/24, 10.0.4.0/24 and 192.168.0.0/24, and four boxes routing between them. Let's say the routing boxes have addresses 10.0.1.1/192.168.0.1, 10.0.2.1/192.168.0.2, 10.0.3.1/192.168.0.3, and 10.0.4.1/192.168.0.4.

You will need to add static routes to the other 10.0.x.0/24 networks on each router box, with commands something like this (EDITED!):

# on the 10.0.1.1 box
ip route add 10.0.2.0/24 via 192.168.0.2
ip route add 10.0.3.0/24 via 192.168.0.3
ip route add 10.0.4.0/24 via 192.168.0.4

and the corresponding routes on the other router boxes. On the non-routing boxes with only one interface, set the default route to point to 10.0.x.1. Of course you will also have to add the static addresses and netmasks on all the interfaces.

Also note that linux does not function as a router by default, you will need to enable packet forwarding with:

echo 1 > /proc/sys/net/ipv4/ip_forward

The ip commands above do not make the settings persistent, how to do that is dependent on the distribution.

As I said, I haven't tested this and may have forgotten something.

Comments

[Dijkgraaf]

Try importing from the lastfm_scrobbles table in smaller batches. It sounds like there may be an issue with that particular table or data there in.

[moustachio]

This seems like a reasonable idea, but the backup I'm importing now is from before I ever had any issues, so it seems pretty unlikely (though not impossible) that there's corruption in the backup...

[moustachio]

Well, since the crash the server won't start so I can't run the query you've suggested, but my config tells me that innodb_open_files = 300. I don't see anything in the config file that says table_open_cache, though...

[greyline]

Thanks for the assistance here. What you advised works when I ping router to router (10.0.1.1 to 10.0.2.1), but not when I ping a different host. For example, pinging from 10.0.1.1 to 10.0.2.2 results in no traffic being routing to the 10.0.2.2 host. Would you suggest doing some iptables manipulation somehow? I've been experimenting with this for a couple days but to no avail.

[Johan Myréen]

Standard routing techniques should be sufficient. Here's a checklist: 1. all addresses should be set up on the hosts (e.g. ip addr add 10.0.2.2/24 dev eth0), 2. the routes should be set up on the four router boxes as above, 3. the default route on the non-router boxes should be set up to point to the routing box on the network (e.g. ip route add default via 10.0.1.1), 4. cat /proc/sys/net/ipv4/ip_forward on the four router boxes should print "1".

[Johan Myréen]

I corrected a mistake in the ip route commands above. I also actually tested this setup, although only with four machines: two routing boxes and two hosts on the 10.0.x.0 sides. It works!

[greyline]

Johan, thanks for your additional input. This setup does work. Turns out the Linux image I was using had default iptables rules that were being problematic. Thank you for helping me in my learning about this.

[ovi]

hi, did you figured it out? can you share your experience? I am facing same error and looking to see what other did to overcome it; it seems the table is corrupted, is there a way to fix it (it is innodb engine) without restoring the whole DB from backup? thank you;