Select Page

How to Disable Safe Updates in MySQL Workbench

So, you’ve just got MySQL Workbench installed and you’ve tried to run some of the handy commands that you’ve picked up from W3Schools (seriously, if you haven’t been through this before, you should!), specifically you’re trying to run a simple UPDATE command in MySQL against a table. Great. But… you’ve noticed that this doesn’t quite work out of the box with MySQL Workbench and it’s likely telling you that you cannot update due to MySQL Workbench being in SAFE MODE. But what is this?

Safe Mode in MySQL Workbench is designed to save you from yourself. Basically it stops you running update commands against anything but a Primary Key. So when you run an update command, you need to specify a Primary Key against the table to ensure you are only updating a single row, rather than wiping out your entire database. While this is great for newbies, it is a little annoying for those more advanced users. When Safe Mode is enabled in MySQL is makes sure rookie mistakes can’t be made, i.e. `UPDATE my_table SET column_name = 123` (whoops…. No ‘WHERE’ command….). #DataGone.

So… It’s time to…. DISABLE SAFE MODE! (at your own peril…aka. You break it….. you fix it!).  But thankfully, you’re a little more experienced than the average rookie. You are READY to DISABLE SAFE MODE!

So, now we’ve got past the #BeCareful section, let’s look at how you exactly disable Safe Updates in MySQL Workbench so you can run all the updates you like and as you see fit.

 

Disable Safe Updates in MySQL Workbench

It’s actually really simple. Follow the steps below within MySQL Workbench;

  • Select from the main menu: Home > Edit > Preferences
  • Click the ‘SQL Editor’ menu item
  • Uncheck the option for ‘Safe Updates (rejects UPDATEs and DELETEs with no restrictions)’
  • Click ‘OK’
  • Now ‘Disconnect’ and ‘Reconnect’ to the server and you’re good to go! Simple!


And just for re-iteration, disabling this can really break things – so be careful! If you aren’t sure what you are doing you can easily overwrite data for column(s) in your entire database by missing a simple command. Triple check every command before running it while Safe Updates are disabled.

How to Copy Data From a Colum in One Table to a Column in Another Table in MySQL

Quick Answer

 

INSERT INTO `new table` (`column z`) VALUES ( SELECT `column a` FROM `old table` )

 

Detailed Answer

Ok, so for those of you looking for a little more information about how/why the above MySQL query works, let’s get into the details. Firstly you likely want to move data from one column in one table in a MySQL database and move this data to another column in another table as part of a refactoring exercise on your database, data and database schema. This is never a simple process and this blog post isn’t going to go into all the considerations you’ll need to make as part of this process, so bear that in mind, instead we’re simply going to look into how to do this basic part of the process and what the details mean.

 

Prepare Your New Table and Column

Get prepared to store your data in a new location by making sure your database table has a new column of the exact same data type as the column you are moving the data from. Any discrepancies here will cause issues, you need to prepare in much more detail if you are migrating data types, which is beyond the scope of this blog post.

 

Migrate Your Data

Now you’ve got your new table and column prepared, it’s time to copy the data from the old table to the new table by running the MySQL command outlined at the start of this blog post. Let’s get into the details of what this means though;

 

INSERT INTO `new table` (`column z`)

 

The above part of the MySQL query is a basic INSERT statement, specifically inserting data into a specific column. Then the next part of the query is telling MySQL what data to insert into this column.

 

VALUES ( SELECT `column a` FROM `old table` )

 

Since the sub-select query returns multiple records, and due to the fact that this sub-select is part of an INSERT statement, MySQL is smart enough to realise that you want to copy the data across.

Now the above isn’t going to work in every scenario and to be fair it really only works perfectly when you don’t have to take into account NOT NULL fields and non-AUTO_INCREMENT fields. Things get significantly more complex when you start to add in these types of…. Complexities, but hey.

Hopefully this is a good starting point to get you thinking along the right lines for migrating data from one table and column in a MySQL database and over to another new table and column with ease as part of your database and data migration processes. Leave a comment with your nuances that you’ve had to deal with along the way – it’s great to share!

How to Setup CIFS on Windows 10 to Share a Folder on the Network

Ever needed to share a folder from your Windows 10 machine so that it is accessible to other computers on the same network? Well, if you do need to do this you’ll soon start learning about CIFS and NFS. For clarity;

  • NFS stands for Network File Share and is used by Linux (so we’re not going to be covering this within this blog post)
  • CIFS stands for Common Internet File System and it is basically the Windows version of Network File Share. More specifically CIFS is actually the public version of SMB, the Server Message Block protocol. The core difference is that CIFS is actually much broader than NFS in the sense that CIFS can support the sharing of printers and serial ports.

So, now we’ve got the definitions out of the way the common use case for setting up CIFS is for easily sharing your files to other computers on a network, for example in an office space you may have a shared file system on a physical server in the corner of the office for example. You’ve got to remember, CIFS was about way before everything moved to the cloud.

 

How to Turn on CIFS on Windows 10

By default, CIFS is disabled on Windows 10 since the vast majority of normal users will never need to use this feature. And as such, since opening up access to your computer so that another computer can access it opens a potential security threat, it is a good thing that this feature is disabled by default. For example, imagine if anyone on your network could just place a virus on your computer, that wouldn’t be good now would it.

So to turn on CIFS, first open up Control Panel then Programs and Features then click on the Turn Windows features on or off. Once opened, select all of the options for SMB 1.0/CIFS File Sharing Support as can be seen below;

This turns things on, next we need to configure the network settings within your Windows 10 machine for the network you are currently connected to. Go to Control Panel then Network and Sharing Center then click on Change advanced sharing settings then select Turn on network discovery and Turn on file and printer sharing. Save the changes and you’re done.

How to Setup CIFS on a Folder

Let’s take a look at an example for how to share a folder. First, let’s create a folder within your main C:\ drive, for example, C:\Your Folder That You Want To Share On The Network\.

Next, simply Right Click and select Properties, then click on the Sharing tab, then on the Share button.

Then select the user who you want to Share this file with and click on Share. Note, that the list of users here are those who are currently setup on your Windows 10 machine. Don’t think of this like modern cloud based applications work in the sense that you share something with someone by sending it to them, this isn’t how this works. Instead, think of this type of sharing as making something accessible to someone else – they still have to configure things at their end to actually view this information. Once complete you’ll see this confirmation;

Depending on your use case, you may want to restrict what people can/can’t do with the content you are sharing. If you go back to the Sharing tab in the Properties popup, then click on the Advanced Sharing options you can see in here that you can configure additional options to secure your data in the way you see fit. You can customise the permissions on the content, such as to allow or deny Read or Write access and you can even limit the number of simultaneous connections.

It’s that simple to do for a very basic setup. You may want to go to lengths to secure user accounts further for example setting up additional users Windows with different levels of access.

How to Access a Shared Folder that has Been Setup Using CIFS on Windows 10 – From Another Computer on the Network

The next step is to access this shared folder from another computer on the same network. This is going to be very dependent on how you want to access this information, for example, are you wanting to access this information from within Windows itself or via an application on the other computer? As such, I’m not going to go into too much detail here other than to let you know that when you are configuring the other computer you are likely going to need to enter in one of the following options;

  • \\DESKTOP-S0K1813\Your Folder That You Want To Share On The Network
  • \\192.168.0.101\Your Folder That You Want To Share On The Network  (i.e. the Static IP Address for the computer that is sharing the file)

And you’ll also likely need to specify the username and password for the computer that is sharing the file.

Hope that helps to get you going in the right direction.

How to Restore all MySQL Server Databases on Windows 10 After a Meltdown

Note: Read this entire set of information multiple times before actually doing anything – you can really do some damage if you don’t know what you’re doing. If in doubt, speak to an expert.

 

Well, this isn’t something you have to do on a regular basis (thankfully!) but you’ll probably need to do this at some point when things go a little wrong.

So, a bit of setting the scene, for myself using Windows 10 for a development machine, all of a sudden something odd happened which looked like either a temporary hard drive failure / blip and / or an automatic Windows Update that didn’t quite work as expected (more than likely…). After not being able to boot onto the machine at all, this resulted in me having to completely re-install Windows 10 from a bootable USB. Thankfully when you do this, Microsoft copies all of your data to a ‘Windows.old’ folder so at least you still have access to everything on your machine. I mean, you’ve got everything backed up in the cloud anyway, so it doesn’t really matter… right?

Anyhow, you now have to task of getting your local MySQL Server back up and running in the exact state they were in before everything went into meltdown. And this is where things get a little interesting.

 

Step 1 – Find Your Old MySQL Server Information

Firstly you need to understand what version of MySQL Server you were running previously as you’ll need to download and install that exact version to avoid any version mismatches. To find this out, open the folder C:\Program Files\MySQL\MySQL Server X.X. Note, this is the original file path where it likely lives. If you had a similar issue to myself, then this will now be within C:\Windows.old\Program Files\MySQL\MySQL Server X.X.

 

Step 2 – Re-Install MySQL Server X.X

Now you know what version of MySQL Server you need to re-install, head over to Microsoft and download that. There is an archive page where you can download different versions of the MySQL installer which may come in handy, https://downloads.mysql.com/archives/installer/.

 

Step 3 – Find your Old Databases and Data

For localhost development, you’ve likely build this up with both genuine, temporary, test and junk databases and data over a period of time. And while you’ll have the schema creation files somewhere as you’ve been building genuine things, along with and migration files – the reality is that to go from nothing then through a number of migrations (for every database) over a period of time – this is going to be a nightmare to do and it highly unlikely to work 100%. Hey, if you have handy full database backup scripts that you run on your local machine on a nightly basis – you’re awesome – but let’s be honest – most people don’t have this (no-matter what they say…). So you’re back to restoring your databases by manipulating the core underlying files that MySQL Server runs from – yay!

So, before we get started, let’s first understand how MySQL Server works and actually stores the data on a Windows 10 machine. There are a few bits of information that you need to understand;

  • C:\Program Files\MySQL\MySQL Server X.X: This is where all of the files live that MySQL Server requires to run. Your actual databases and data aren’t stored within here.
  • C:\.Program Data\MySQL\MySQL Server X.X\data: This is a hidden file and this is where all of your databases and data are stored. It is these files that we’ll be using to restore your entire MySQL Server databases and data.
  • C:\.Program Data\MySQL\MySQL Server X.X\data\{your_database};
    • {your_database}.frm: FRM is a file extension for formatting used with MySQL. FRM stands for FoRMat. FRM files are used to define the format of a table used with MySQL. MySQL is a cross-platform relational database.
    • {your_database}.ibd: The IBD file type is primarily associated with MySQL – InnoDB by Sun Microsystems, Inc. InnoDB tables and indexes can be stored in their own file (a feature called “multiple tablespaces” since in this way each table uses its own tablespace).
    • {your_database}.opt: opt is just overall database options file. They contain no data.

 

Step 4 – Stop the MySQL Server Windows Background Service

If you’ve just installed MySQL Server again, it has likely automatically started and/or been configured to start as part of the Windows startup process. Since you’ll be changing one of the files and importing the old ones, this step just stops things failing along the way.

To do this, type;

  • CTRL + R (to open the Run box)
  • msc (to open the Windows Services)
  • Find ‘MySQL Server X.X
  • Right Click
  • Select: Stop

The service will now be stopped.

 

Step 5 – Rename your New ‘Data’ File

Remember I said that your databases are stored in C:\.Program Data\MySQL\MySQL Server X.X\data? Well, since you’ve just done a fresh install, it’s likely that you’ll have one of these folders created already. So go ahead and rename that to ‘data-old’ – You know, just in case anything goes wrong with the next step.

 

Step 6 – Move All Your Old Databases and Data

Next, simply move the entire /data/ folder that you want to restore and place it in C:\.Program Data\MySQL\MySQL Server X.X\data. Simple.

 

Step 7 – Start the MySQL Server Windows Background Service

Now you’ve copied all of your old databases and data over into the current installed folders, then you’re good to start the Windows service again now.

 

Step 8 – Verify the Restored Databases and Data in MySQL Workbench

Now you’ve restored the databases and data, let’s just double check that everything is there as you’re expecting. Open up MySQL Workbench, connect to your MySQL Server and verify the databases are listed and there is data in the tables where you would expect to see them.

Depending on how you installed MySQL Workbench, it is likely that you also installed the newest version of MySQL Server – so make sure you’re connecting to the correct version of MySQL Server that is running on your system as you may have multiple versions.

You can download older versions of MySQL Workbench from here and use the installer to set it all up, https://downloads.mysql.com/archives/workbench/, although if you’ve installed the latest version of MySQL Server already and likely the latest version of MySQL Workbench too, then before you install an older version of MySQL Workbench, I’d just try connecting to the older version of MySQL Server on your machine – just put it on a different port than the standard 3306, put it on 3307 or something like that if you aren’t using that port for anything else.

 

Notes

The above steps clearly aren’t going to work for everyone – but hopefully this is a starter for 10 if you’ve come across this problem and are in the process of trying to resolve.

There is also MySQL Utilities which may come in handy for more challenging restorations, https://downloads.mysql.com/archives/utilities/, you’ll need to have Python 2.6 installed (it doesn’t work on newer versions of Python), so download that from here if you need to, https://www.python.org/download/releases/2.6/. And you’ll also need Microsoft Visual C++ 2005 Redistributable Package (x64) installed too if you’re using MySQL Utilities, so download that here, https://www.microsoft.com/en-us/download/confirmation.aspx?id=21254. If you try to run MySQL Utilities once it’s installed without having Python 2.6 and the C++ package mentioned, then you’ll likely get the error “The command line MySQL Utilities could not be located. To use them, you must download and install the utilities package for your system from www.mysql.com. Click on the Download button to proceed.”

Good luck and feel free to comment with any tips / pointers you’ve found helpful along the way.

How to Create a DOS Bootable USB

So, you’re probably reading this blog post after lots of searching online about how to do this, likely after lots of reading of hardware manufacturer’s documentation about how to upgrade firmware or similar. And you’ll probably also realise that we live in 2019 (at the time of writing….), so when a hardware manufacturer recommends you install some software on a ‘floppy disk drive’ you realise that the hardware world is stuck in the stone age. But hey. It is what it is, and we have to deal with this nonsense.

So, let’s figure out how to get this firmware upgrade onto a ‘floppy disk drive” aka a USB Drive!

A bit of background before we do though. It used to be pretty easy to boot into a DOS environment when Windows owned the world back in 1995. But things have changed a little since then and we have things like non-Microsoft software and other hardware made by many manufacturers around the world. This is great, but does complicate things a little.

DOS is an operating system that runs from a hard disk (aka. Disk Operating System) – but typically refers to MS-DOS, i.e. Microsoft Disk Operating System.

Ultimately if you need to upgrade the firmware on a piece of hardware on a machine, then you need to Boot the Machine to your DOS USB (i.e. configure the Boot Priority in your motherboard BIOS to boot from your USB, not Floppy…., Disk Drive).

What this means in simple terms is that you need to have on your UBS Drive is the Bootable USB Operating System (more on how to do this shortly…) PLUS the Firmware upgrade files (likely a Shell or Bash or Executable file).

 

Create a DOS Bootable USB

If you’ve simply tried to move the firmware upgrade files to the USB and boot directly to the USB drive with these files present you’ll be sorely disappointed as you’ll probably have received an error along the lines of “firmware upgrade shell load.exe is not recognised as an internal or external command

What this basically means is that you cannot simply run the USB from Shell, but you must run the command from DOS instead. Semantics, but you’re working with underlying hardware here so you must be specific, so let’s create a DOS Bootable USB.

Firstly, download Rufus here, https://rufus.ie/, it’s pretty awesome, and more importantly allows you to create a bootable USB. Once you’ve opened Rufus, fill out the details as follows;

 

 

Prepare the Firmware Upgrade and Upgrade It

Basically, once you’ve created your DOS Bootable USB above, copy whatever files over to the same USB Drive as you’ll need to work with these once you’ve booted into the system.

Next, once you’ve booted into your DOS, simply run whatever commands your firmware upgrade tells you to.

And that’s it! This is a simple step 🙂 

How to Customise the Welcome Text When SSHing Into a Server

Ok, so if you’re reading this, you’re probably a bit of a geek like myself. We’ve all seen the “Welcome Mr Stark” message that appears on Iron Man when Tony Stark logs into his private server. i.e. in other words, SSH’ing into his server.

 

 

So, how easy or difficult is it to get this set up? Well, it’s remarkably easy to configure this and it is all down to configuring your ‘Message of the Day’ on your server. So let’s have a play around.

Firstly, generate some text that you want to display, you could simple try the basis such as “Hello”, but that’s a bit boring. Instead, let’s look to replicate the same message that we can see on Tony Start’s private server. You’ll find many different ASCI Art Generators online if you search, so find one that you like the look of. Here are just a few examples that I’ve generated to give you an idea of the different styles you can use.

 

 

So once you’ve got the text that you want to configure, simply SSH into your server and run the command;

 


sudo -s

 

Which will switch your user over to the Sudo user, you’ll need to enter in the password to do this.

Next, run the command;

 


nano /etc/motd

 

Which will create a new file for you, if it doesn’t already exist, or edit the current file if it is already there. Feel free to use the editor of your choice here, personally I prefer the Nano editor as I find it more intuitive to use. Simply paste your text into here, and save the file.

Simple, you’re done. Then the next time you login to your server via SSH you’ll be greeted with the nice message that you entered;

 

 

 

How to Create a Bootable USB for Ubuntu Server

If you’re reading this blog post then you’re probably in the realms of playing with hardware and building a mini server, media server, web server or something along those lines. Great! So now you’ve got your hardware pieced together, it’s now time to start installing software on it. For the purposes of this blog post, I’m not going to go into the details of what/why/when/who/how for the vast array of choices and options available for configuring software on your server. Instead, I’m just going to look specifically about how to create a bootable USB for installing Ubuntu Server. Simple and straight to the point.  

 

Step 1: Install Universal USB Installer 

Download here, https://www.pendrivelinux.com/universal-usb-installer-easy-as-1-2-3/ 

This software will turn a .iso file into a bootable partition on a USB drive.  

 

Step 2: Download Ubuntu Server 

Download the relevant version here, https://www.ubuntu.com/download/alternative-downloads#alternate-ubuntu-server-installer 

Note, make sure you’re downloading the correct version for what you require, there are quite a few different versions! 

This step will download the .iso file to your computer. You’ll need this file in the next step.  

 

Step 3: Create the Bootable USB 

Follow the instructions here, https://www.pendrivelinux.com/universal-usb-installer-easy-as-1-2-3/ 

In essence, you’re installing the .iso file you downloaded in the previous step onto your USB Pen Drive. Note, make sure you install on the correct drive on your computer or you can really really really mess things up! 

When you are creating the bootable USB for Ubuntu Server, make sure you actually select Ubuntu Server from the dropdown list, as there is also an option for installing Ubuntu (desktop) version too, which you don’t want.  

 

Complete! 

That’s it! You’re done. You’ve just created yourself a bootable USB that can be used for installing Ubuntu Server. Simply plug this device into your server, make sure that it has priority boot settings so your installer can then run and you’re on your way! 

 

Note for Ubuntu Server 18.04

The above software doesn’t seem to like Ubuntu Server 18.04, so you’ll probably need to use the Rufus software instead – https://rufus.akeo.ie/

 

How to Loop Through a Map in Java Using JSTL on a JSP

Quick info for reference.
Loop through a Map and output the Key and Value data using;

<c:forEach items="${map}" var="entry">
  Key: <c:out value="${entry.key}"/>
  Value: <c:out value="${entry.value}"/>
</c:forEach>

If you have an object within the Key:Value pair, such as within an object Map(), you can get this data as follows;

${map.key}

Then you can iterate over the above list using the code to get the Attribute data within the CustomerInformation object a follows;

<c:forEach items="${map.value}" var="customerInformation"> 
  <tr>
    <td>${customerInformation.firstName}</td>
  </tr>
</c:forEach>

How to Increase the Maximum Connections on Apache Tomcat

The maximum number of connections that Apache Tomcat can handle is defined within the Tomcat settings. In essence, this setting determines the number of concurrent connections that can be handled by Apache Tomcat.

Out of the box, Apache Tomcat is configured to handle around 200 simultaneous connections (kind of…), which for most web servers and projects is more than adequate. But when you get into the realms of millions of monthly active users and concurrent connections, you’ll soon need to optimise Tomcat to suit your needs accordingly.

Ultimately though, the number of simultaneous connections that the Apache Tomcat software on your web server is capable is handling is determined by the physical limitations of your web server. The bigger your hardware, the bigger the number of connections that Apache Tomcat is capable of handling. This being said, the 200 maximum connections as a default is merely an arbitrary number, so you’ll need to assess this in relation to the hardware you are running on. The bigger the server hardware you’re running on, the higher the number of maximum connections on Apache Tomcat your server will be able to handle. As always, this can change depending on what you are doing. For example, if you have a process on your really fast server that is calling a third party dog slow server, then this soon becomes the limiting factor in the chain.

Max connections from an Apache Tomcat perspective is calculated based on a number of factors such as the number of simultaneous connections multiplied by the maximum execution time that each thread is capable of handling. Under normal circumstances this can appear to be behaving correctly, so keep in mind exceptional circumstances when things don’t quite perform as you wish. What you’ll soon spot (via the Tomcat Manager) is that the Max Connections soon starts to creep up and eat up all the resources on your server. This can be caused by many factors, from long running processes on your own server, or code on that is calling third party servers which may be running slow and causing problems.

With all this in mind, let’s take a look at how to increase the maximum connections on Apache Tomcat. It’s actually quite simple to do.

Run the command;

 


sudo nano /usr/share/tomcat8/conf/server.xml

 

This command will open the editor to edit the file Server.xml which controls many of the settings required for configuring Apache Tomcat. You’ll note that it is Apache Tomcat 8 that is being used in this example, so edit this accordingly for the version of Apache Tomcat that you are using. Again, depending on your individual configuration of your server, this file may live in a different file path. So to easily find the file if the above command doesn’t work, then run the command below;

 


find / -type f -name server.xml

 

The above command will search for a File (f) called “server.xml”

What you’re looking for within this file is the setting for “maxConnections” which will have a value set. You may not have this variable set so look out for the core settings for the port that your Tomcat instance is listening on, for example;

 


<Connector port=”8080” protocol=”HTTP/1.1” maxConnections=”200”/>

 

The maximum number of connections that the server will accept and process at any given time. When this number has been reached, the server will accept, but not process, one further connection. This additional connection be blocked until the number of connections being processed falls below maxConnections at which point the server will start accepting and processing new connections again.

Depending on your specific requirements, you may also need to increase the maxThreads setting too. Hope that helps getting you started with tuning Apache Tomcat for your specific needs.

How to Make Apache HTTPD Service Start Automatically on Server Reboot and Startup

If you’re looking for a quick answer and you’re running a common version of Linux, just type this when you are SSH’d into your server;

 


sudo chkconfig httpd on

 

Now for a bit more technical details about what this all does.

 

What is chkconfig?

The Chkconfig command in Linux is used to setup, view, or change services that are configured to start automatically during the system startup which abstracts some of the underlying settings that are stored within the /etc/rc.d/init.d/ directory. Chkconfig basically helps you to easily make service start or not when your system reboots.

Some handy chkconfig commands that you’ll find handy;

  • chkconfig –list (dash dash)
    • This will list the details of all services that are running on startup
  • chkconfig –list httpd (dash dash)
    • This allows you to list specifically the details of the service you care about, to avoid you having to trawl through the entire list. Simply change ‘httpd’ to whatever service name you are interested in looking at.

When you start to look through the outputs from the above commands, it may be a little confusing seeing 7 levels, some with a status of on and some with a status of off, but what do they all mean? The different levels are what is known as Linux Runlevels. Generally speaking, you probably don’t need to worry about those at all. But if you do need some fine grain control over what happens and when, when that may be covered in a future blog post.