Compatibility between Linux and Windows based databases

[Xavier_Bit]

Hi!
I develop on a WAMP server environment (under Windows) where I can easily switch back to former versions, can use my image processing programs and have easy access to my programming IDE as well as direct access to the DB tables.

I just realized, that Gibbon bears a big problem as it uses uppercase database table names, which are converted into lowercase as soon as they are loaded into a Windows environment (Windows does not support uppercase).

This works on the local installation (as Windows won’t check for uppercase) but will fail to be restored on the Linux based webserver and makes it almost impossible to exchange the data between development and release servers.

I’m in the process to setup a Linux virtual machine just for this problem and save the changes I did.

In this very old post from 2018

Database backup from Local Host to Cpanel

It looks like your issue may be related to a lower_case_table_names` setting in your system: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html It appears Windows systems set lower_case_table_names` to 1 by default, and Unix-based systems set it to 0. Based on the documentation above, try changing this value to 2 on your localhost (& restarting it). Here’s a link that has some instructions: https://stackoverflow.com/questions/2659684/why-my-tables-names-are-converted-in…

Sandra mentions a script to revert the table names into their original state:
‘I’ve attached an SQL snippet that when run should fix the case of your table names’

which unfortunately isn’t present anymore. Any possibility this is still around, maybe in an updated form to save me some time copying out all the original tables and writing a script?

I would highly suggest to rework the database table names for future releases, using the more accepted underline between words, as this poses a real problem possibly when migrating from Windows based IIS webservers to Linux based ones.

[AndroidOL]

Try:
mysqldump -u root -p --all-databases --no-create-db --lower-case-table-names > dump.sql

Typically, our database is independent.

[Xavier_Bit]

Yep, I got that part. Applying ‘Find and Replace’ the whole list on a sql dump from my Windows environment for the almost 200 table names was a bit a hitch and without the right tools not really feasible. Good thing I had a something lying around which did the job in a batch.

I’m not sure what you mean with the ‘database is independent’. At the actual state it isn’t OS independent, as loading it to a Wampp server will change the uppercase letters in all lowercase. This works until you want to export the database to a Unix / Linux environment where it stops working, as there ‘gibbonaction’ is different to ‘gibbonAction’. This table should be named ‘gibbon_action’ which would be crossplatform.

[AndroidOL]

Typically, the database should be installed and stored on a dedicated server with support for offsite backups or cold backups. When operating outside the development environment, it is not recommended to use integrated tools like WAMP. Instead, independent deployments should be used, such as Redis + MySQL (MariaDB) or Nginx + PHP-FPM, to ensure stability and maintainability.

Each application is independently virtualized to meet disaster recovery requirements. Even for smaller applications, we recommend migrating within the same environment (including the operating system and dependencies). The advantage of virtualization is its snapshot support, allowing for quick restoration when needed. Additionally, the database should be independent of the web server and, if possible, utilize cloud-based database solutions for enhanced security and scalability.

Gibbon has relatively low server requirements. If using a cloud platform, a 4-core, 8GB RAM instance can efficiently support thousands of students and faculty members. The primary cost concern is the high price of bandwidth.

It is rare to hear of Windows being used as the primary environment for web hosting (aside from IIS-based solutions). In comparison, Windows itself consumes significantly more system resources than Debian + Gibbon.

[AndroidOL]

Upper to Lower

#!/usr/bin/env python3
import re

pattern = re.compile(r'(?i)(CREATE TABLE\s+(?:IF NOT EXISTS\s+)?)(`?)(\w+)(`?)')

def lower_table(m): 
    return f"{m.group(1)}{m.group(2)}{m.group(3).lower()}{m.group(4)}"

try:
    with open('dump.sql', encoding='utf-8') as f:
        sql = f.read()
except Exception as e:
    print(f"Error reading dump.sql: {e}")
    exit(1)

new_sql = pattern.sub(lower_table, sql)

try: 
    with open('dump_lower.sql', 'w', encoding='utf-8') as f:
        f.write(new_sql)
except Exception as e:
    print(f"Error writing dump_lower.sql: {e}")
    exit(1)

Lower to Upper

#!/usr/bin/env python3
import re, sys
try:
    import wordninja
except ImportError:
    sys.exit("Please install wordninja: pip install wordninja")

pat = re.compile(r'(?i)(CREATE TABLE\s+(?:IF NOT EXISTS\s+)?)(`?)(gibbon)(\w*)(`?)')
def repl(m):
    prefix, bt, gibbon, rest, at = m.groups()
    new_rest = ''.join(w.capitalize() for w in wordninja.split(rest)) if rest else ''
    return f"{prefix}{bt}{gibbon}{new_rest}{at}"

try:
    sql = open('dump.sql', encoding='utf-8').read()
except Exception as e:
    sys.exit(f"Error reading dump.sql: {e}")

new_sql = pat.sub(repl, sql)

try:
    open('dump_camel.sql', 'w', encoding='utf-8').write(new_sql)
except Exception as e:
    sys.exit(f"Error writing dump_camel.sql: {e}")

[Xavier_Bit]

That’s about what I did, Python to the rescue!

Nevertheless, migrating from the development environment to the webserver should be possible without having to do this.

There is this other thing, called IIS and I’m quit sure, trying to migrate from there to Unix / Linux bases servers would yield the same problem.