Tag Archives: cytanium - Page 2

Restore MySQL Dump File to a Different Database Name

Restore MySQL Data to a Different DatabaseToday I had to take a MySQL database backup from one database and then restore it to a different database name (for development/testing purposes). I tried a number of different tricks that I found online through Google without any success. The issue was that the user I was using to restore the data was NOT an administrator (root) on the database so it was failing with permission errors – always with an error related to the name of the database I restored FROM.

Okay, time to go old-school. So… I opened the .sql file that was created by the MySQL backup and behold – it’s text! How great is that!? Not some cryptic gibberish like a SQL Server database backup file format.

When looking at the file a little, right there near the top I found the issue. The MySQL backup files by default assume you want to restore to the exact same file name. So then have this line:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

In that line the database that I restored from was actually named “test”. As you can see, it checks for the existence of the database and if it doesn’t exist, it creates the database. Well, in this case I didn’t want that database created and my user didn’t have permissions to perform that action anyway.

So I changed the name of the old database (“test”) to match the name I wanted for the new database (“newTest”).

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `newTest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

Great – one error avoided! But thankfully, before existing the .sql file, I noticed the very next line in the file:

USE `test`;

Well, that’s no good. That’s going to use the ORIGINAL database that I performed the restore from. Yikes. If my user had permissions to that database it would have overwritten the original. Not good at all. So, let’s change that…

USE `newTest`;

I did a quick scan through the rest of the file and nothing stood out to me as an issue. I performed the restore (in this case using MySQL Workbench but any tool – even a command line – would have worked fine) and everything went nice and smoothly. Like magic I now have the data from the “test” database restored to my new database named “newTest”.

I hope this is helpful! Cheers, and happy hosting!

PUT/POST/DELETE Verb Errors On Site

A client was getting errors when using PUT/POST/DELETE verbs on their web application recently.

The errors he was seeing were:

<h2>405 - HTTP verb used to access this page is not allowed.</h2>
<h3>The page you are looking for cannot be displayed because an invalid method (HTTP verb) was used to attempt access

After some troubleshooting the error was isolated to the fact that WebDav was installed on the server and was intercepting those requests for its own service use.

Rather than removing WebDav from the server, we went looking for another solution. Thankfully someone on Twitter understood the issue and gave an example of changes to make to the client’s web.config file in order to disable (remove) the WebDav module for just that specific site without requiring any manual administrative actions on the server.

The code updates to make to your web.config file to resolve this error are:

<configuration>
  <system.webServer>
    <handlers>
      <remove name="WebDAV" />
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
    <modules>
      <remove name="WebDAVModule" />
    </modules>
  </system.webServer>
</configuration>

I hope this help. Happy hosting!

WordPress: Redirect One Domain to Another

I have multiple domains pointed to certain WordPress installations that I run. Some people have setups like this and run multiple sites in their WP install. I, on the other hand though, wanted to redirect all requests for certain domains to a single domain. Basically consolidating the domains so the users all wind up on what I decide as the primary domain regardless of which domain they enter.

I first tried to do this with Redirection but that plug-in doesn’t seem to quite handle things the way I wanted.

After some stumbling around I found a setting within WP itself that does the trick – no plug-in or anything else fancy needed.

The setting to redirect WordPress URLs

What I did was change the “WordPress Address (URL)” and “Site Address (URL)” settings (boxed in red above), which are found on the Settings->General options page, to both be the primary domain name exactly as I wanted it to be. Then, presto, it auto-redirected all traffic to that domain.

Now, let me note, when I first did this, the site broke for a little while. The reason was that WP was now “looking” for things in a new location and my local browser had some old data cached. It was fine in reality (for the rest of the world) but looked broken for me. After just about two minutes, the cached data seemed to have resolved itself and everything was fine.

So, to summarize with an example, I have both www.site1.com and www.site2.com pointing to a WordPress installation. But, if WP notices the www.site2.com domain it performs a redirect and reloads the site with the www.site1.com domain as I designated.

Nice trick. I hope this tip helps.

Ultimate TinyMCE

I just installed Ultimate TinyMCE because I’ve been struggling with getting table formatting to work – and stay working – in WordPress.

I know HTML so have been switching to the HTML tab and defining tables with placeholder text, then switching back to the visual tab to edit the content. Switching between the tabs isn’t ideal though and sometimes the process of switching between them seems to cause issues where the raw code gets reformatted – changing the way the page looks.

So let’s see if this tool will make it easier to create and manage tables.

The quick brown fox jumped over the lazy dog. The quick brown fox jumped over the lazy dog. The quick brown fox jumped over the lazy dog.
The quick brown fox jumped over the lazy dog. The quick brown fox jumped over the lazy dog. The quick brown fox jumped over the lazy dog.

Yeah, that was pretty easy! I clicked the Insert Table icon (this plugin adds more icons right there to the visual tools button area!), selected three columns and two rows and – bam! – instant table. I think I’m going to like this tool; and tables are just one of many things it claims to make easier in WordPress.

Happy hosting.

SQL Server restore failure and resolution

A friend on mine had a recent issue where the restore of a SQL Server backup file – taken on one server and being restored to a different one – wouldn’t complete. It would continually throw an error of:

Server was unable to process request. —> File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TheDatabase_log.ldf’ is claimed by ‘YAF_log'(2) and ‘ftrow_YafSearch’.

That made no sense and the names in the error message (I changed the DB name to “TheDatabase”) didn’t match (before my change I mean :>) anything in reality. The database didn’t already exist, and those other two file names, which the error states have some sort of “claim” on the log file, didn’t match anything in the destination system.

After a lot of troubleshooting with no luck, he stumbled across some information online that helped him get past it. I asked him to summarize so I could post here in case it helps someone else – or for reference for myself if I ever run across the issue.

His summary to me:

The issue seems to happen when there is a secondary database file (NDF) in the BAK file. The SQL restore doesn’t seem to like NDF files if the file does not already exist.

If this happens with a dedicated server you just create the files first (step 4), then restore, and you’re good to go. With a shared hosting account you have to do a bit more.

Steps to fix:

1. Download and install SQL Server Express to a local computer, if you do not have access to SQL Server already. You will need file level access to the SQL Server so you cannot do this connected to a shared hosting SQL Server instance with SSMS.
2. Connect to your local SQL Server instance.
3. Create a new, blank database with the same name as the database you need to restore.
4. Open a SQL query and run the following command, replacing ‘path\file.bak’ with the full path to your BAK file, INCLUDING the double single ticks (‘ ‘) surrounding the path. This will create all the necessary files needed to restore the database.

RESTORE FILELISTONLY FROM DISK = ‘path\file.bak’

5. Restore the database to the local server.

NOTE: If you get stuck in “DBname (Restoring…)” mode after the restore run the following command (WITH RECOVERY must be on a separate line):

RESTORE DATABASE DBname
WITH RECOVERY

6. You will now need to merge the NDF(s) with the MDF. For each NDF file run the following command, replacing the names appropriately:

USE DBname
DBCC shrinkfile(‘file.ndf’,emptyfile)
ALTER DATABASE DBname
REMOVE FILE logical_file_name;
GO

You can get the NDF file name by navigating to your SQL Data folder or running this query:

SELECT name, physical_name AS current_file_location
FROM sys.master_files

You can get the logical_file_name from the Files section or the name column from the above command.

7. Backup the database from the local SQL Server instance and restore it to the shared hosting SQL Server instance – it should work fine this time!

Hopefully this is helpful to someone else in the same situation. Happy hosting!