DW can't open large sql database files

[paulk7737514]

I exported a database using PHPmyAdmin and am unable to open it with DW. I get nothing but a blank page. However it does open with Notepad and I can see all the tables and data.

The file is rather large and includes two tables with 15 megabytes (form items and metadata) which I suppose I could empty, but I don't understand why the file opens in one application that is free on my Windows machine vs. an Adobe application for which I pay a monthly subscription fee!

[Nancy OShea]

Not sure what you're trying to do but SQL data needs to be imported into your database with phpMyAdmin, not opened in DW. 

1. DW is not an SQL editor.

2. DW supports the following file types:

[paulk7737514]

Hi Nancy. I selected DW as the program to open SQL files because when I download a copy of a site's database I can open it in DW and save it in a site folder I name DB Backup or something similar. I'll just use Notepad instead.

[Nancy OShea]

I do that too.  But I accomplish it without opening a file.  Just save the SQL file to your default downloads folder and move it to your target destination.  Start to finish, it takes me 20 seconds or less.

[B i r n o u]

I was never able to open large file in DW... what ever content and extension are... SQL, JSON, XML.... worth, when lucly I was able to open a large SQL file and launch a search and replace... DW stuck.. then crash... it's a long long long time knowed trouble...

the best ever tool for manupulating SQL, is from far Note Pad++, on the search and replace, encoding, regex manipulation... and so on...

yes, I agree it's a pity that DW can't handle this correctly.

[Liam Dilley]

This is not a workflow you should be doing.

It is not quite clear with your setup if your understanding a setup...

To run something as a localhost that is a dev/staging environment you need the web server features and SQL database to manage that.

If it is an apache/php/sql setup on the server you will need something like XAMPP setup and configured so it has the Apache and PHP environment and for you to then apply the SQL database there.

An SQL database is data and in a series of tables of data keys and their values. There is nothing in Dreamweaver you would be opening up that data to manage and edit.

If you are just looking to make bulk adjustments to database data you would be writing code to do that. Either direct or if is a CMS or platform driving it using their API to do that. OR you will be using an SQL database editor tool.

[B i r n o u]

@Liam Dilley excuse me, actually I don't understand the point you are trying to make. or maybe I don't understand the OP's question.

In fact, have you ever had to work on a SQL file as a text based database. it's something I practice daily.

Certainly PHPMyAdmin, or any other tools are interfaces that allow is to do it, but very often it is more practical, fast and efficient to intervene directly on the SQL DUMP with a code editor.

and that's where Dreamweaver often crashes, unlike editors like Note Pad++ or Sublime Text, but for that I have a large preference for Note Pad++

could you confirm if I have understood correctly or not the question of the OP and the point you want to address by talking about Xampp apache PHP ????

[Liam Dilley]

I have to really disagree about editing a text based version of an SQL database over any tool that can run command scripts on. That is crazy. Doing so on large complex relational databases would likely break them and take a lot longer!

I think reference Subline Text and Notepad++ shows where the space is in knowledge of modern code editors. If you get an amazon AWS instance the default on there these days is not Notepad++ any more 🙂

In terms of the OP, I was looking at why they would be trying to open an SQL database in any form in DW.

1. As I stated, editing in that form today vs the tools, apps and scripting to do that, especially with any form of relational database and even semi complex key structures, especiall if the solution has GUID referencing is crazy. I would be suprised if people can do it quicker that the actual modern tools SQL Manager that are there for that purpose. SO why they need to open in DW is still in question as a result.

2. I have seen people who have wordpress hosting they got and try to have a local copy running and do not understand about the setup you need to be able to run that locally. It is not the 1st, 5th or 10th time I have seen someone say they got their database and can not get their machine to run the website thinking if they get the SQL database it will just work.

DW will not ever do that and Adobe would be crazy to try to build that all in. It is already over bloated and slow compared to most modern editors. DW I started out and much old love for but its not on anyone's top editor lists of editors for site in 2020/2021.

[B i r n o u]

I'm far from being a reference, and I don't want to teach anyone anything (I'm still learning every day myself) but it's the first time I hear that working in text mode on a SQL database can cause some problems (unless, of course, you don't know what you're doing)

I've been working on databases since uh... actually the web didn't exist yet that I was already working on databases... I think in 1979 on stuff like SEQUEL and then especially DB2... and since all this time, not a day has gone by without me working on databases without using a text mode... well, I'm not talking about Mongo, Couch or any other type of database which, of course, remains glued to my code editor, but I'm talking about SQL type databases.

there are so many things that can be done in a flexible, simple, fast and efficient way directly on the SQL file.
but as my almost friend @osgood_  often says that, I'll end up believing that my working methods are antediluvian... and not adapted at all... but I can't change my mind (so easily) and I think I'll continue to export my Wordpress DUMPs (especially those that are very verbose), or PIWIK (which are even more verbose) and work on them with NotePad++ which is currently the best adapted (to my certainly outdated methods) in terms of encoding (and inter-encoding management) and especially with its magnificent regular expression management

[Liam Dilley]

What is the biggest SQL database in GB you have edited in text and how many tables did it have?

[B i r n o u]

in fact you take the problem in reverse... the question is not to compare neither the size of the database, nor its number of tables...

in fact, two tables identical in number of records can contain a few dozen characters per fields for one and a few tens of thousands for the other. it doesn't mean anything....

so, there are various elements to take into account... 

first, what is important is to know the file size limit that the code editor you are using can handle. For my part I use NotePad++ and after 300 Mb it becomes rock'n roll...

note that on that case DW is long long long time ago... simply out , in better case it crashed, or in worth case it frozen for hours.... and the spinning pizza wont stop to turn

second, the question is what I have to do on the database, which tables are concerned, and the Dump will only concern this aspect of the database, not the all DB.

third, it is especially and above all to know the work to be done, and why precisely a code editor is much better adapted than any other 'embedded' tool...

so all this put down, here is the answer to your question,

the last work (yesterday) I did, was working on a 250 Mo dump, the work itself concerned a Wordpress datas, and was to correct an encoding trouble, a DNS change, a move from http to https, and a flatten of old HTML entities...

NotePad++ open the full file in less than 10 seconds 180 000 real lines, I mean a lot of record contains 20 000+ caracters so it displays on a large fake lines (line wrapped)... the all work took less than 5 minutes (all my regex are snipped in evernote... too bad that NotePad++ still doesn't offer a save and load queries)... 🙂

and Dreamweaver would never have been able to handle this kind of work. and that's a pity for a code editor

Does this answer your question ?

[Paul-M]

SQLYog is good windows tool for working with MySQL/MariaDB .... There is a free community version and the paid version has some good sync and diff tools so you compare you local development databse's schema and/or data with remote.

[Nancy OShea]

Navicat has a 14 day free trial.  It isn't cheap but it works with multiple DBs and is available for Win, Mac or Linux. 

https://www.navicat.com/en/products

[BenPleysier]

@Paul-M  and @Nancy OShea

I think That the OP, @Liam Dilley  and @B i r n o u are talking about is working on an SQL Dump file which is a normal text file. Notepad++ would certainly be my choice when Dreamweacer can't handle the size.

[B i r n o u]

this is also what I thought, it seemed to me that the OP @paulk7737514  wanted to intervene directly on the DUMP. on the other hand I am not sure that @Liam Dilley  shares this solution, or idea 😉

[Paul-M]

You can import dump file into SQLyog edit it, and export to SQL file ... I guess it's a matter of how you want to or are used to working....

[B i r n o u]

@Paul-M correct me if I'm wrong, but SQLyog is not a code editor but a visual graphic tool to edit and monitor the Database ?

because, in fact, we're talking about code editor.

[Paul-M]

It is a visual editor for MySQL/MariaDB with advanced diff and sync tools in the paid version

[B i r n o u]

In fact the original discussion came from the fact that Dreamweaver (premium account) is not able to open and manipulate SQL files, and that an application like NotePad++ (free) can.

So the topic is based on the possibility to edit a database from a code editor.

I don't think the idea is to find a visual alternative, but I'm perhaps wrong. Besides Ben is not mistaken and says so in this discussion sub-section. So !

[Paul-M]

SQLYog is a tool specfically for working with MySQL/MariaDB ... I think we're allowed to offers alternative solutions, unless the forum police are out in full force .... I don't know why anyone would be trying to edit a large SQL file in Dreamweaver, it doesn't handle large files of any kind too well ....

[B i r n o u]

I don't know why anyone would be trying to edit a large SQL file in Dreamweaver, it doesn't handle large files of any kind too well ....

By @Paul-M

notepad++ does....  and it's free, that was the original question ... 

" why the file opens in one application that is free on my Windows machine vs. an Adobe application for which I pay a monthly subscription fee! "

I don't think I have forbidden any alternatives, if you have felt such a thing, I am sorry, and it was not my intention. Again... I'm sorry.

what I think I'm saying awkwardly, given your reaction, would be to propose an alternative in the form of a code editor that can act on the SQL, in textual form, as TXT search and replace with Regex in it... and could handle file larger that 300 mo which is the limit of NotePad++

just curious, <no sarcasm... just curious> for person that only use graphical interface, or interface as PHP MyAdmin, have you ever modify TABLE or DATAS in a SQL file using a code editor ? </no sarcasm... just curious>

and if not... I'm curious... I don't say that it is impossible... but how do you reencode differently records that have been mixed on old age database.... or how do you modify records to change DATAS (in WordPress plug in for just naming one), where datas in records are stored as esacped string containing properties / values that has to be accorded... and so on...

and finaly how much time that it takes to handle all of it ? my answer in a code editor with encoding and regex as NotePad++ provides...it takes less than 5 minutes, as I indicate in a previous comment to @Liam Dilley