Devart Forums

JORGEMAL

I review the project you sent and I see the following differences.
My connectionString includes a "schema = restaurant" (or Initial Schema) entry and yours does not. I added it to the connectionString in your project, ran the backup and then tried to run the result in the pgAdmin III's SQL Editor. The result is an error message saying that the «areas_servicio_clave_seq» relation does not exist. Nevertheless, as soon as I remove the "schema" entry from your project's connectionString, everything works correctly. I also commented out the dump.Schema = "restaurant" in your project and left the schema entry in the connectionString with the same results. So, the only way your project works is when no schema is defined in the connectionString.

I tried to replicate this setting in my project without success. I removed the "schema" entry from my connectionString leaving only the pgDump.Schema = "restaurant" in my VB code and what I got was a file that does not include any INSERT statements. I sent a sample of such a file.

So, in short, it seems that including the "schema" or "Initial Schema" in the connectionString is leading to some kind of problem. Please note that all of my connectionString entries in my project are located in the web.config file.

This is the connectionString I use in my project:

This connectionString does not have a schema entry and was used for testing purposes only:

The user "administrador" has all the rights in the database.

***** Please disregard the file I sent, the backup/restore works fine if I remove the "schema" entry from my connectionString. The only problem is when such an entry is if such an entry is included. *****

This is the modified connectionString from your project in order to work in my environment:
Dim connectionString As String = _
"Server = 127.0.0.1; user id = postgres; password = password; port = 5432; Database = restaurant; Unicode = true;". The only difference is the "Server" and "password" values.

Respectfully,
Jorge Maldonado

JORGEMAL

I guess I did not explain myself correctly. The problem I am having is NOT with pgSqlDump class, at least not directly. The last piece of VB code I sent last time (Protected Sub Respaldo) ends without any exception and does not terminate unexpectedly. The files that contain the backup data are supposed to be correctly generated. The problem comes when performing the restore operation. I open the files generated by the dump process (using SQL Editor of pgAdmin III) and click the button to execute the query. It is at this moment that the operation fails. I sent 3 files named losajos.backup, luciareposteria.backup and teresajos.backup. These files were generated by my small dump application and these are the ones I open and execute in pgAdmin III without success.

However, I will go over the project you sent and perform the necessary tests. I will inform you about the results.

Regards,
Jorge Maldonado

JORGEMAL

The following is the code that performs the backup. The SUB receives 2 parameters:
1. strNombre is the name that the backup file will have.
2. strConnStr is the connection string to connect to the DB.
There is a control called txtUbicacion (a textbox) that indicates the path to the folder where the dumped file will be saved. Actually, I run this process for 3 DB's so I get 3 different backup files.
I connect to my postgres server as the postgres user who is a superuser.

I have sent you the following information:
1. 3 backup files gotten from pgAdmin III backup utility in compressed format. I specify WIN1252 as the encoding choice. Their names are LuciaReposteriaComp, RestaurantLosAjosComp and TeresAjosComp.
2. 3 backup files gotten from my small app. Their names are LuciaReposteria, RestaurantLosAjos and TeresAjos. When I load and run the script for RestaurantLosAjos I get the message saying that the aspnet_personalization relation does not exist. When I load and run the script for the other 2 files I get a syntax error. I am really lost with this situation and I very much appreciate your support and patience.

With respect,
Jorge Maldonado

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Unicode = True
        pgConn.Open()

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.IncludeDrop = True
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

JORGEMAL

When I upgraded my project to dotConnect for PostgreSQL 5.10.111, my User Controles (.ascx files) were not modified, I did it manually. I have such files in a folder named UserControls. I will appreciate your comments.

Regards,
Jorge Maldonado

JORGEMAL

I upgraded to dotConnect for PostgeSQL 5.10.111 and there is still a problem. I am getting the following error message:

ERROR: no existe la relación «restaurant.aspnet_personalization»
SQL state: 42P01

In English this means that the «restaurant.aspnet_personalization»
relation does not exist.

I review the backup file and I see that the "aspnet_personalization" table is dropped and never created. I wonder if there is another issue similar to this one. I sent the backup file for your review. I guess this is the problem unless I am missing something.

Regards,
Jorge Maldonado

JORGEMAL

I want to make another comment to this issue and provide additional information.
Actually, I have a backup of my site's database that I got using pgAdmin III; it is compressed and with Win1252 encoding. The rest of the parameters are left unchanged. I created a new web site in my production server, restored the database to it and user validation worked fine. In this case I see that the applicationname in both sites is the same (/) and maybe this is the reason why it works correctly. If I add a new user account in my development PC, the applicationname assigned to it is different than those the already exist. The applicationname in the restored database is "/"; the applicationname of a new account is the name of the folder in my development PC.

The following are the answers to your 4 questions:
1. I am using the web.config file to set the machineKey values.
2. I am not changing te applicationname anymore.
3. I perform the backup in the production server as described at the beginning of this reply. I restore to my development PC using pgAdmin III and checking 2 options: "Clean before restore" and "Single transaction". I get a "Process returned exit code 0." at the end of the restore process.
4. I am sure I use a valid user account, in fact, it is my personal user account.

The login process is made using the "asp:login" control, I only added code to catch the specific error in case there is one. The ID of the control is ctrLogin and this is the code I added:

Code: Select all

Protected Sub ctrLogin_LoginError(ByVal sender As Object, ByVal e As System.EventArgs) Handles ctrLogin.LoginError
        ' The username is ALWAYS converted to uppercase.
        ctrLogin.UserName = ctrLogin.UserName.ToUpper

        Dim userInfo As MembershipUser = Membership.GetUser(ctrLogin.UserName)
        Dim ctrLabel As Label = ctrLogin.FindControl("lblMensajeError")

        If userInfo Is Nothing Then
            ctrLabel.Text = "The user account does not exist in the database."
        Else
            If Not userInfo.IsApproved Then
                ctrLabel.Text = "The user account is not approved."
            ElseIf userInfo.IsLockedOut Then
                ctrLabel.Text = "The user account has been locked out."
            Else
                ctrLabel.Text = "Invalid user account."
            End If
        End If
    End Sub

The condition that is met in the "if" statement is the first one so the message I get when I try to validate is "The user account does not exist in the database."

I hope this information is useful.
Regards,
Jorge Maldonado

JORGEMAL

Here is the information you requested:

1. ASP.NET 3.5 in both production server and development PC.
2. dotConnect for PostgreSQL 5.10.96.0
3. I do not get an exception if the "applicationname" field of table "aspnet_users" remains as it is in the production server (which actually is /) but I cannot validate my user credentials because I get a message saying that the username does not exist.
On the other hand, if I change the "applicationname" field of the "aspnet_users" table to the one that corresponds in my development PC I get the exception "Provider cannot decode password". Here is the exception information:

Server Error in '/Restaurant 01.00.01' Application.
--------------------------------------------------------------------------------
Provider can not decode password
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Configuration.Provider.ProviderException: Provider can not decode password

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[ProviderException: Provider can not decode password]
Devart.Common.Web.Providers.DbMembershipProvider.a(String A_0, MembershipPasswordFormat A_1) +172
Devart.Common.Web.Providers.DbMembershipProvider.a(String A_0, String A_1, MembershipPasswordFormat& A_2, String& A_3) +230
Devart.Common.Web.Providers.DbMembershipProvider.ValidateUser(String username, String password) +51
System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +60
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +119
System.Web.UI.WebControls.Login.AttemptLogin() +115
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3615; ASP.NET Version:2.0.50727.3618

4.
4.a) validation="SHA1" decryption="Auto"
4.b) Yes, I got the validationKey and decryptionKey from https://www.grc.com/passwords.htm (64 random hexadecimal characters). You can see a reference to this web site at the end of step 3. The keys are the same in both machines.

Regards,
Jorge Maldonado

JORGEMAL

I have been trying to restore a database dumped with pgSqlDump and I am getting an error message. I will describe this issue here and please let me know if this situation has to do with Devart.
The error message is as follows (I am translating it to English because it is in Spanish):
--------------------------------------------------------------------------
ERROR: duplicate key violates uniqueness restriction >.
DETAIL: The key already exists (applicationname)=(/).

********** Error **********
ERROR: duplicate key violates uniqueness restriction >.
SQL state: 23505
DETAIL: The key already exists (applicationname)=(/).
--------------------------------------------------------------------------

I have checked the "aspnet_roles" table and I do not see that the "applicationname" field has to do with a key of any kind. I do not even find a key with a name of "aspnet_roles_applicationname_key" in the dumped file. I have sent the file that contains the backup in case you need it. I am using pgAdmin III SQL Editor to restore the DB. My PostgreSQL version is 9.0.1. What I notice is that the error message refers to (applicationname)=(/) and such an application is running in a production web server; on the other hand, I want to perform the restore task in my development PC so, the applicationname is different. Nevertheless, if I get a compressed backup using pgAdmin III, I can successfully restore it to my PC using pgAdmin III too.

This is the VB code:

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Unicode = True
        pgConn.Open()

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.IncludeDrop = True
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

Regards,
Jorge Maldonado

JORGEMAL

I have a web application running in a production server and I made a backup of the database which includes the membership tables. After that, I restored it to the PC where the project is being developed. The "applicationname" field in the "aspnet_users" table in the production server is different than the one in my PC. I tried to type my credentials and I am not able to be validated so I changed the "applicationname" to match that of the production server and another message tells me that the provider cannot decode the password. The machineKey entries in the web.config file are the same in both machines (validationKey, decryptionKey, validation and decryption). What else should I consider in order to bring the users in the production server membership tables to my PC.

Best regards,
Jorge Maldonado

JORGEMAL

I just upgraded dotConnect for PostgreSQL from version 4.95.180 to 5.10.96 and I found that many changes were made to parts of my web project where characters like ñ, Ñ, á, é, í, ó, ú were found. I use this characters because I develop applications in Spanish. This post is only to suggest NOT to make any changes to web forms like these because, as it happened to me, I did not notice such changes and I had to work to recover things back to the way I need. I use a tool from a provider that, when an upgrade is applied, it just notifies that a web form has this kind of characters and does nothing to it, letting you make changes manually only in such forms. If there already is a workaround for this issue please let me know and disregard this post.

Best regards,
Jorge Maldonado

JORGEMAL

It is working now. I ran DBMonitor and the transaction commited successfully. I will continue testing and contact you in case I need.

Thank you very much,
Jorge Maldonado

JORGEMAL

I upgraded to dotConnect for PostgreSQL 5.10.96 and the dumped file was read by pgAmin III SQL Editor. Nevertheless, I found the following issues:

1. When I open the file with SQL Editor I get a message that says "The file contains mixed line endings. They will be converted to the current setting". What does this mean?

2. I ran an Explain Query and an error appears as follows (I included translation from Spanish to English):

Query result with 0 rows will be returned.
ERROR: error de sintaxis en o cerca de «SET»
(This means "Syntax error in or near SET).
LINE 2: SET client_encoding = 'UTF8';
^

********** Error **********
ERROR: error de sintaxis en o cerca de «SET»
(This means "Syntax error in or near SET).
SQL state: 42601
Character: 60

This error is always thrown even if I remove 1 line, 2 lines, 5 lines from the file.

3. You suggested to set the conecction's Unicode property to true. I suppose this will not cause any problems with characters like "Ñ". I reviewed the backup file and everything looks fine but I wonder if, when performing a restore, things will work fine too.

Best regards,
Jorge Maldonado

JORGEMAL

Below is the modified code where I define the Unicode property before openning the connection. I also commented such a line and used the Charset property. Only one property was used at a time. The sample I sent uses the Unicode property, not the Charset.

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Unicode = True
        'pgConn.Charset = "WIN1252"
        pgConn.Open()

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

Regards,
Jorge Maldonado

JORGEMAL

[quote="JORGEMAL"]This is the modified code in 2 versions, which includes setting the Unicode property to True, but I am still getting the same message when I try to open the backup file with SQL Editor:

"The file 'LOSAJOS.backup' could not be opened because it contains characters that could not be interpreted.".

I even tried to use the Charset property of the connection to "WIN1252" (which is the one I use in my DB) without success. I tested with and without it.

Am I missing anything?
Regards,
Jorge Maldonado

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()
        pgConn.Unicode = True
        pgConn.Charset = "WIN1252"

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()
        'pgConn.Unicode = True
        'pgConn.Charset = "WIN1252"

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Connection.Unicode = True
        pgDump.Connection.Charset = "WIN1252"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

JORGEMAL

This is the modified code, which includes setting the Unicode property to True, but I am still getting the same message when I try to open the backup file with SQL Editor:

"The file 'LOSAJOS.backup' could not be opened because it contains characters that could not be interpreted.".

I even tried to use the Charset property of the connection to "WIN1252" (which is the one I use in my DB) without success.

Am I missing anything?
Regards,
Jorge Maldonado

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()
        pgConn.Unicode = True

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

Devart Forums

Search found 171 matches

Restoring users from production server to my development PC

Suggestion for upgrade process