texts: Mysql - Rows not updated in transactions without any errors

texts:
I have a stored procedure that updates a user's balance and inserts a transaction log. I don't know why, but sometimes it doesn't update the users table.

Stored Procedure Code

DELIMITER $$
USE `db_name`$$
DROP PROCEDURE IF EXISTS `update_balance`$$
CREATE DEFINER=`username`@`%` PROCEDURE `update_balance`
    (IN balancechange INT,
     IN userid INT,
     IN tableName VARCHAR(100),
     IN `type` INT,
     IN description  VARCHAR(1000) CHARSET utf8,
     IN trans_id VARCHAR(150))
BEGIN
START TRANSACTION;
SET @tmp = (SELECT id FROM users WHERE id = userid FOR UPDATE);
UPDATE users SET gold = @gold := ((@oldbalance:=gold) + balancechange)
    WHERE id = userid AND (gold + balancechange) >= 0;
SET @countRow =  ROW_COUNT();
IF (@countRow > 0) THEN
    SET @t1 =CONCAT("INSERT INTO ", tableName,
                    " (user_id, type, balance_change, old_balance, new_balance, description, trans_id)
                    values ('", userid, "', '", `type`, "', '", balancechange, "', '", @oldbalance, "', '", @gold, "', '", description, "', '", trans_id, "')");
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT @oldbalance AS oldbalance , @gold AS gold,
           balancechange, 0 AS result;
ELSEIF (balancechange = 0) THEN
    SET @oldbalance = (SELECT gold FROM users WHERE id = userid LIMIT 1);
    SET @t1 =CONCAT("INSERT INTO ", tableName,
                    " (user_id, type, balance_change, old_balance, new_balance, description, trans_id)
                    values ('", userid, "', '", `type`, "', '", 0, "', '", @oldbalance, "', '", @oldbalance, "', '", description, "', '", trans_id, "')");
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT @oldbalance AS oldbalance , @oldbalance AS gold,
           0 AS balancechange, 0 AS result;
ELSE
    SET @oldbalance = (SELECT gold FROM users WHERE id = userid LIMIT 1);
    SELECT @oldbalance AS oldbalance , @oldbalance AS gold,
           0 AS balancechange, -1 AS result;
END IF;
COMMIT;
END$$

DELIMITER ;

Usage

CALL update_balance(1000, 123, 'user_transaction201806', 1, '{$desc}', '123456789')

The users table is InnoDB, the user_transaction201806 is MyISAM.

Sometime the data for a user isn't updated in the users table, but the data for a user is still added in the relevant transaction table (e.g. user_transaction201806) with correct data.

Because the table containing the user's transactions is in myISAM format, the transaction cannot be rolled back.


texts: Performance effect of indexing foreign keys

texts:
My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.

There are two tables involved. The first is a ratings table with 2 columns: ID (Integer, Primary Key) and Description (Varchar). It will only have 4 rows (poor, average, above average and excellent) and will "never" change.

The second table is a review table. There will be 1 row for each employee every six months. So we are looking at thousands of entries and not millions. Each row has approximately 25 evaluation fields, which are foreign keys into the rating table. Examples would be typical questions where employee is rated on Completing Work on Time, Works well with others etc...

From what I've researched, there doesn't appear to be any real reason to create an index on each of those keys since I will never be adding and/or deleting from the ratings table. Are there any other considerations that I should take into account? Or am I worrying about nothing since by db is so small?


texts:
I am trying to write a stored procedure to search by DB for whatever is inserted. see below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_login_tb`
(
 IN p_pass varchar(100),
 IN p_email_id varchar(100),
 IN p_name varchar(45),
 IN p_staff_id varchar(45),
 IN p_status_id varchar(1)
)
BEGIN

    set @sqlquery = 'select * from kupo_101.login_tb where login_tb.id is NOT NULL';

    IF (p_pass is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.pass = "',p_pass,'"');
    ELSEIF (p_email_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.email_id = "',p_email_id,'"');
    ELSEIF (p_name is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.first_name = "',p_name,'"');
    ELSEIF (p_staff_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.staff_id = "',p_staff_id,'"');
    ELSEIF (p_status_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.status = "',p_status_id,'"');
    END IF;

    prepare stmt from @sqlquery;
    execute stmt;

END

MariaDB [kupo_101]> CALL get_all_login_tb('qewr13241wwe','','','','');
+----+--------------+------------------+----------------------+----------+--------+
| id | pass         | email_id         | first_name           | staff_id | status |
+----+--------------+------------------+----------------------+----------+--------+
|  2 | qewr13241wwe | iimama@gmail.com | Imama Igein Blessing | 10122ASD | Y      |
|  3 | qewr13241wwe | barvo@gmail.com  | ucee oluchi          | 10122AFF | N      |
+----+--------------+------------------+----------------------+----------+--------+
2 rows in set (0.00 sec)

But when use other filed to search it shows empty Set.

CALL get_all_login_tb('','','','10122ASD','');
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


texts: What is the most effective way to discover all running instances of SQL Server using PowerShell?

texts:
I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem to find all the instances.

1) Use WMI

        $srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName
    $instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}}
    return $instances

2) Use remote registry (as with Get-SQLInstance1)

The biggest problem I'm running into is that not all of the servers that I know about are running with the SQL Server WMI provider nor are all of them allowing remote registry. Is there a third method? I can use Remote Desktop to access all the servers but I'm looking at approximately 30 machines and would like to avoid manual steps if possible. This only needs to work for SQL Server 2008 and higher and while it would be nice to know about the other SQL Server services (SSIS/SSAS/SSRS) my main focus is on SQL Server itself.


texts: Does Postgres ever require a naked field without single quotes?

texts:
I have this query:

UPDATE user_map_table SET  user_id = '1', value = '{"bob":"diane"}', key = 'couple', value_type = 'string', added = 'false' WHERE user_id = 1

it worked fine. The thing is the user_id column is an int column, and the added column is a boolean column. Postgres doesn't seem to care that I put single-quotes around the '1' and 'false'.  Is there ever a time where single quotes will make Postgres barf?

One obvious answer might be if you have extra whitespace:  ' 1' or 'false ', but other than that?


texts: How to use parent table column inside subquery JOIN condition?

texts:
I am writing a SQL Query for pulling records from multiple tables for sending the update or added new values email notifications to the user.

In below SQL Query I am getting all records which are updated in respective tables, But I want to add one conditional check/JOIN in each LEFT JOIN satatements
for AssociatedRecordID Column of NotificationQueue Table so that I will only pull only matched records from each select tables to increase the performance of query execution.

For example:

LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.NoteID = nq.AssociatedRecordID

Here I want to use nq.AssociatedRecordID for Join AND nt.NoteID = nq.AssociatedRecordID

Query:

SELECT ops.* FROM NotificationQueue nq
    INNER JOIN (
        SELECT op.OpportunityID, op.Name AS [OpportunityName], ua.Email AS OwnerEmail, nt.NoteID, nt.Note,
            tks.TaskID, tks.ActivityType, ua.FirstName, ua.LastName, FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
            lead.LeadID, lead.LeadName
        FROM Opportunity op WITH(NOLOCK)
        INNER JOIN UserAccount ua WITH(NOLOCK) on op.owner = ua.UserID
        LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
        LEFT JOIN (
            SELECT t.TaskID, t.TaskTypeID, t.OpportunityID, tt.Description AS [ActivityType] FROM Task t WITH(NOLOCK)
            INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
        ) tks ON op.OpportunityID = tks.OpportunityID
        LEFT JOIN (
            SELECT l.LeadID, l.Topic AS [LeadName], lo.AssociatedOpportunityId FROM LeadOpportunity lo WITH(NOLOCK)
            INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
            WHERE l.Active = 1
        ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

        WHERE op.Active = 1
    ) ops ON nq.RecordID = ops.OpportunityID
WHERE nq.Processed = 'N'

Output:

I am getting expected records from above query but I want to use AssociatedRecordID column of NotificationQueue Table in all LEFT JOIN statements for better performance,
Any suggestions How should I achieve same would be appreciated.


texts:
I'm in the process of migrating some databases from iso8859-1 to utf-8. One of the databases contain 1000+ tables, loads of procedures, functions, triggers, constraints etc. I would like to automate the process as much as possible since this is going to happen several times, and for several instances of the system. Ideally I would like to hand over the work to my friends Jenkins and Ansible ;-)

My plan was to generate the ddl with db2look, but it fails to generate the "objects" in correct order. I tried running both with and without the -ct flag (fails for different reasons). For example:

connect to <db>;
create table t1 (x int not null);
create unique index t1pk on t1 (x);
alter table t1 add constraint t1pk primary key (x);
connect reset;

running

db2look -d <db> -e -td @ -ct

generates the wrong order for the index and the constraint.

CREATE TABLE "DB2INST1"."T1"  (
              "X" INTEGER NOT NULL )
             IN "USERSPACE1"
             ORGANIZE BY ROW@

ALTER TABLE "DB2INST1"."T1"
    ADD CONSTRAINT "T1PK" PRIMARY KEY
            ("X")@

CREATE UNIQUE INDEX "DB2INST1"."T1PK" ON "DB2INST1"."T1"
            ("X" ASC)
            COMPRESS NO
            INCLUDE NULL KEYS ALLOW REVERSE SCANS@

Removing -ct works in this trivial example, but fails in the actual database due to other dependencies.

I imagine migrating to UTF-8 is a quite common task so I'm curios on what people have done. The two possible solutions I see is to:

a) write a parser that inspects the catalog for database
   objects and sort them topologically using dependency tables.

b) write a parser that reads the output from db2look, identify
   each object and sort them topologically using dependency tables.

Clearly there are drawbacks with both of these, am I overlooking some trivial way to migrate the databases?

EDIT: An additional observation is that as long as an index does not contain any extra attributes compared to the implicitly created index through a unique / primary key constraint a QL0605W warning is raised. If on the other hand additional attributes are specified a SQL0601N error is raised. Example:

CREATE UNIQUE INDEX X1 ON T1 (C1, C2)
COMPRESS NO
INCLUDE NULL KEYS
DISALLOW REVERSE SCANS

generates a warning SQL0605W if a similar index is created via primary key statement.

On the other hand an index like:

CREATE UNIQUE INDEX X2 ON T2 (C1, C2)
INCLUDE (C3)
CLUSTER
COMPRESS NO
INCLUDE NULL KEYS
ALLOW REVERSE SCANS

generates an error SQL0601N. I assume this is due to the INCLUDE clause, but perhaps the CLUSTER clause will cause this behavior as well.


texts: Getting postgreSQL binaries without third party tools

texts:
Where can I download postgreSQL binaries without third party tools like pgAdmin, stackbuilder?

From licencing point of view, can I download binaries from https://www.enterprisedb.com/download-postgresql-binaries and delete pgAdmin, stackbuilder folders and distribute the remaining folders with my product?

Is there any other source where I can download only postgreSQL binaries for linux?


texts: Does Oracle Partition by Reference optimizes join queries

texts:
Oracle 11g has a new feature in partitioning tables called partition by reference.

I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining two tables or now?

How can one be sured of that?


texts: SSRS 2016 Report Manager screen hanging on "Loading...." when creating a subscription to a report

texts:
I am planning a migration to 2016 Reporting Services and have noticed a particular problem. On two separate new installs for SSRS 2016 Developer Edition, I am unable to save a subscription to a report. The report was downloaded as RDL from the current 2008 R2 server, then saved to the new 2016 environment. The new server is build 13.0.4466.4. I also installed the same version of SQL onto a second server and have reproduced this problem. All servers are running in Native Mode.

Within Report Manager, I am able to view the contents of the report (on-demand). When I try to create a subscription, the web page is stuck with the "Loading...." prompt after I click 'Create subscription'. It does this regardless of the Render Format I choose. I am attempting to deliver via e-mail. I have domain credentials for the SSRS service account configured, and I have put the IP address for the SMTP server in RS Configuration Manager, so I know it is configured for email ok.

In the SSRS log file, I see these types of errors at the time I try to create the subscription:

    library!ReportServer_0-1!2888!03/06/2018-16:19:17:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.NotEnabledException: , Microsoft.ReportingServices.Diagnostics.Utilities.NotEnabledException: The requested functionality is not currently enabled.;
extensionfactory!ReportServer_0-1!2888!03/06/2018-16:19:17:: i INFO: Skipped instantiating Report Server PowerBI report server extension. Extension was not enabled.
library!ReportServer_0-1!2888!03/06/2018-16:19:17:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: , Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: This operation is not supported on a report server that runs in native mode.;
library!ReportServer_0-1!2668!03/06/2018-16:19:18:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.NotEnabledException: , Microsoft.ReportingServices.Diagnostics.Utilities.NotEnabledException: The requested functionality is not currently enabled.;
extensionfactory!ReportServer_0-1!2668!03/06/2018-16:19:18:: i INFO: Skipped instantiating Report Server PowerBI report server extension. Extension was not enabled.
library!ReportServer_0-1!2bf0!03/06/2018-16:19:18:: i INFO: Call to GetPermissionsAction(/Pre-Migration/Open Activity Report). User: XXXX\xxx.
library!WindowsService_0!2518!03/06/2018-16:24:35:: i INFO: Call to CleanBatch()

In the Windows Application log, I see these events repeated:

(Event ID 137)
The Report Server PowerBI extension failed to read its configuration settings from the RSReportServer.config configuration file. The extension is not available to users.

PowerBI Integration is not enabled on either of the 2016 servers, and is obviously not a part of the existing 2008 R2 configuration. I do not wish to use this feature.

How do I solve this problem and begin to create subscriptions, so I can ultimately migrate all of the reports?


texts: Software for backing up SQL Server databases

texts:
Since using the new version of Symantec, I've experienced lots of problems doing incremental backups of clients' SQL databases.

Is Windows Server backup a good alternative to Symantec Backup Exec 2012 for backing up SQL databases?


texts: How to import values of a JSON to PostgreSQL?

texts:
I have a JSON file gugu.json that looks like this:

{
"key1": {
          "value1": "normal",
          "value2": [
                     "PogU"
                    ]
        },
"key2": ...
}

I want to put all the contents of the JSON file into the table

KEKW(value1 text, value2 text, ...).

I already tried many thing like this:

CREATE TABLE gugu(
 data json
);

\copy gugu FROM 'gugu.json';

SELECT * FROM json_populate_record(null::KEKW, (SELECT data->'key1' FROM gugu));

But as you can see I just don't know how I would get the "second level" of JSON in the table?


texts: Triggers on Insert or Update SQL Server (Spatial)

texts:
I am looking at creating a trigger on a live SQL Server spatial table that will update a column when a user either:

Creates a new feature
Updates the geometry of a feature in a GIS application

Unfortunately I have only been able to work out updating a column when a new feature is created, or updating a column when the geometry column is changed, but not both within one trigger.

Ideal world - attribute Updated populates current date for the particular feature that has been created or modified.

This is what I have so far that works only when creating a new feature:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER *[triggername]* ON *[table]*
AFTER INSERT,UPDATE
AS BEGIN
    DECLARE @ID VARCHAR(8)

    SELECT @ID = [ID] FROM *[table]*

    UPDATE *[table]*
    SET Updated = GETDATE()
    WHERE [ID] = @ID
END
GO

This works when the geometry is changed

CREATE TRIGGER [triggername] ON [table]
FOR INSERT, UPDATE
AS BEGIN
    UPDATE [table]
    SET [table].Updated = Getdate()
    FROM [table]
    INNER JOIN deleted d ON [table].id = d.id
END
GO


texts: SQL Server 2012 service will not start after each server reboot - corrupt SQLBOOT.DLL

texts:
When I tried to start the service manually from a command prompt, I get a dialog stating:

Your SQL Server installation is either corrupt or has been tampered with (Unable to load SQLBOOT.DLL (HRESULT 0x8007007e)).  Please uninstall and then re-run setup to correct the problem.

I ran the Repair utility from the Maintenance section of the Setup and that resolved the problem. However, the next time the server was rebooted I had to go through the same process again. And then the next time the server was rebooted, I had to do it yet again.

The ERRORLOG is unhelpful, only showing messages from the shutdown, not the failed start attempt:

2016-04-20 19:35:45.70 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2016-04-20 19:35:46.04 spid25s     Service Broker manager has shut down.
2016-04-20 19:35:46.04 spid25s     Error: 17054, Severity: 16, State: 1.
2016-04-20 19:35:46.04 spid25s     The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
2016-04-20 19:35:46.05 spid5s      .NET Framework runtime has been stopped.
2016-04-20 19:35:46.21 spid5s      SQL Server shutdown has been initiated
2016-04-20 19:35:46.21 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

What would cause this corruption every time the server restarts?


texts: automatic undo for version control in mysql

texts:
I'm not a mysql expert, but it seems like there is no obvious way to do that.
I'll explain what I want.
Let's say I'm doing a data migration which would update some rows in some table.

I'd like to be able to make a backup, execute migration script and get undo script automatically.

I don't see why this should be impossible, since rdbms stores all changes in a log already for transaction (mysql has --log-bin option for example) and they already know how to revert them (because they can rollback a transaction). So maybe it's possible to extract and use this data for the purpose of creating undo scripts for migrations?

EDIT.

Okay, so thanks to one of the answers I've found that mariadb actually partially support what I want already. This feature called flashbacks. It's actually doing what I've suggested - translating INSERTS into DELETE via binlog. So what I am missing is the upcoming support of flashbacks over DDL statements and making flashbacks only for one transaction.


texts: MobiLink: How to propagade schema change for a synch profile to remote databases

texts:
Once an application is online with a synchronisation profile it might happen, that the profile needs to be changed. E.g. a new index is introduced, columns are added etc.

Having the synchronisation profile created with Sybase Central, what's the designated way to propagate these changes to remote databases? The only option here seems to be to complete new create a profile or update current one including complete regeneration of remote database.


texts: create another mysql database help performance?

texts:
I want to know if I use different databases can help performs.

I have one big table in support_chat with more than 5 million rows and I need to scan this all  the time (3s with ajax) to check new messages on support. If I move this table to another db in the same computer, can it help anything? (let users, sells, comments table faster)

eg:

db1:

    users table
    sells table
    comments table
    support_chat table // move this table to db2

db2:

suport_chat table


texts: procedure for postgres to create table if not exist

texts:
i want to create table if that doesn't, i tried below code:

create or replace function create_table() returns void as
$$
begin
    if not exists(select *
                  from   pg_tables
                  where  schemaname = 'Public'
                  and    tablename = 'test')
    then

        create table test
        (
            the_id int not null,
            name text
        );

    end if;

end;
$$
language 'plpgsql';

While executing this procedure first time:

select creat_table();

table gets created but when I execute it again I get the below error:

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create table test
        (
            the_id int not null,
            name text
        )"
PL/pgSQL function create_table() line 8 at SQL statement
********** Error **********

ERROR: relation "test" already exists
SQL state: 42P07
Context: SQL statement "create table test
        (
            the_id int not null,
            name text
        )"
PL/pgSQL function create_table() line 8 at SQL statement

How to achieve this, and also I want to call this procedure from Informatica pre-sql target session property so i want to call procedure with table name as parameter.


texts:
I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a SQL Script. I cannot use the SQL Agent because this is the express edition.

Currently, I am writing a script which backs up each database one by one. However, this is quite high maintenance - every time a database is added, dropped, or renamed, I have to modify this script to reflect these changes. I would instead like to write one single SQL script which backs up all databases at once, without the need to explicitly backup each database one by one.

This is currently the script used to backup a single database:

Print 'Backing up MyDatabase...'
BACKUP DATABASE MyDatabase
TO DISK = 'F:\Backups\SQL Databases\MyDatabase.bak'
   WITH FORMAT,
      NAME = 'Full Backup of MyDatabase';
GO

How can I go about enumerating a list of all databases and performing a loop to do this backup to all databases?

Just some additional notes of the backup process:

Scheduled Task in Windows executes a batch file at 9:00 PM daily
Batch file calls OSQL which executes the SQL script
The OSQL outputs a text file saving the results
There are about 30 databases to be backed up
Some of the databases are over 2GB (so it will take some time)
A third-party backup service obtains the resulting backup files and pushes them off-site at midnight every night


texts: Deploying to an existing database from VS 2010 on TFS

texts:
I am trying to find a way in VS2010 to add a few objects to an existing database without scripting out all of the existing objects. This is a maintenance project that is adding a table, altering an existing table by adding a column, creating a foreign key constraint between them and altering a stored proc to use a new column in the existing table rather than a hard coded case statement. I tried a database project and then converted it to SSDT in an attempt to find a way to not drop the database.

The scripts generated from this database project generate drop and create statements for the database. If I manually remove the database portion of the scripts, the result is exactly what I need.  Is there a way to force the project to NOT drop and recreate the database, or is there a project type I can use instead?


texts:
I want to use autovacuum for vacuum and analyze in PostgreSQL 11.5.
I am trying to enable it on a dev server but I can't get it started.

According to documentation :

autovacuum = on
log_autovacuum_min_duration = 0
#autovacuum_max_workers = 3     # max number of autovacuum subprocesses
                # (change requires restart)
autovacuum_naptime = 60     # time between autovacuum runs
autovacuum_vacuum_threshold = 0
autovacuum_analyze_threshold = 0
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before     vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before     analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                # (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000    # maximum multixact age
                # before forced vacuum
                # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                # autovacuum, in milliseconds;
                # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for
                # autovacuum, -1 means use
                # vacuum_cost_limit

Analyze and vacuum thresold are set to 0 to make autovacuum start on the dev server where there is no activity.

I added as mentionned : track_counts = on

Then I enabled autovacuum on just one table because I realized that reloptions field in pg_class was NULL. Now I can read {autovacuum_enabled= true} for my table.

I reloaded the server conf many times and checked the statistics of my tables with pgadmin4, especially the columns last autovacuum and last autoanalyze. They are still empty.


texts: 'ambiguous column name' error refers to database, table, and column guuu

texts:
sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as 'sourcedb', the table as 'answers', and the field as 'markup'. Why is this query triggering the error?

sqlite> SELECT answers.*
        FROM   sourcedb.answers
               INNER JOIN sourcedb.questions_tests
                 ON sourcedb.questions_tests.testskey = '121212eczema'
               INNER JOIN sourcedb.answers_questions
                 ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey
               INNER JOIN sourcedb.answers
                 ON sourcedb.answers.answerskey = sourcedb.answers_questions.questionskey;
...>    ...>    ...> Error: ambiguous column name: sourcedb.answers.markup

Some additional detail...

sqlite> pragma table_info(answers);
0|markup|TEXT|0||0
1|identifier|TEXT|0||0
2|text|TEXT|0||0
3|answerskey|INTEGER|0||1
4|answertype|TEXT|0||0
5|ReadPerms||0||0
6|UpdatePerms||0||0
7|DeletePerms||0||0
sqlite> pragma database_list;
0|main|
2|sourcedb|/root/.ttest/database/sqlite/ttest-simple.sqlite


texts: LGWR is switching frequently in every 3,4,7 minutes. what should i do

texts:
I am running Oracle 11g R2 single instance on Linux 6.7.
Data guard is also configured.

In a database I have 4 groups of redologs, each group has 2 members and each logfile size is 200mb.

The problem I am facing is that LGWR is switching every 4 minutes. The output size of archivelogs are maximum 90mb to 100mb.

What should I do to solve this problem?


texts: Can SQL Server 2014 server reported drive space properties change overnight when database growth is set to Unlimited?

texts:
Our SQL Server 2014 server is running on Windows Server 2012 R2. The drive space properties reported by the OS shows that we had 17 GB free one day, and the next day it shows we have only 718 MB free. No particular queries nor SQL Jobs were ran during this time. The database maxsize is set to Unlimited (growth is set to 10%), and executing this query:

SELECT (
           SELECT SUM(CAST(df.size AS FLOAT))
           FROM   sys.database_files AS df
           WHERE  df.type IN (0, 2, 4)
       )                   AS [DbSize],
       SUM(a.total_pages)  AS [SpaceUsed],
       (
           SELECT SUM(CAST(df.size AS FLOAT))
           FROM   sys.database_files AS df
           WHERE  df.type IN (1, 3)
       )                   AS [LogSize]
FROM   sys.partitions p
       JOIN sys.allocation_units a
            ON  p.partition_id = a.container_id
       LEFT JOIN sys.internal_tables it
            ON  p.object_id = it.object_id

...to get the DB size vs space used, shows we are actually 12 GB below the current database size.

So is it possible that Windows suddenly updated the reported space available, taking into account the unused portion of space?


texts:
I've got a standalone MongoDB server with a lot amount of data on it. Let's say, it's srv1 server. I've decided to make a sharded cluster, so I've created additional servers srv2 and srv3.

We have:

srv1 - existing mongodb server with data, that will be shard0001.
srv2 - new mongodb server, that will be shard0000.
srv3 - also new mongodb server, that will be config server with running mongos instance. Mongos instance running only on that server.
vk - database I want to be sharded.

On srv3 I've started a mongos with --configdb rs0/127.0.0.1:27002 option and a mongod with --replSet rs0 --configsvr --dbpath /data/config --port 27002.
After that, via the config instance i've added srv1 and srv2 as a shards with sh.addShard("srv1-2:27017") command.
Also, on srv2 I've created an empty database and collections with the same names as on srv1. After that on config server i've executed sh.adminCommand({enableSharding: "vk"}); command.

What I have now:

Surprisingly, it's working. When i'm trying to read/write something to my database, using srv3 as connection point, it's working perfectly! When i've did something before (using srv1 standalone), average response time was about 2 seconds, now it's 0.02 seconds.

The problem I have:

Well, the sharding is not working. Database on srv2 is empty. Here is the output of the db.printShardingStatus() command from srv3:

--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("5d81f91bdde81f72ac4d1a0a")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "srv2:27017",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "srv1:27017",  "state" : 1 }
  active mongoses:
        "3.6.3" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  5
        Last reported error:  Connection refused
        Time of Reported error:  Wed Sep 18 2019 14:41:13 GMT+0200 (CEST)
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
                config.system.sessions
                        shard key: { "_id" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000       1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0000 Timestamp(1, 0)
        {  "_id" : "shard0000",  "primary" : "shard0000",  "partitioned" : true }
        {  "_id" : "vk",  "primary" : "shard0001",  "partitioned" : true }
        {  "_id" : "vk:",  "primary" : "shard0000",  "partitioned" : true }

Additional info:

The balancer says Connection refused, but i've opened 27017 and 27002 port on every machine in cluster. It's still not working.
In mongos (srv3) log there are a lot of successful connections both to srv1 and srv2. Telnet says the same - everything is fine and opened.
I've added shards without --replSet options. Mongod instances on srv1 and srv2 working without that options too.
I didn't created any chunk keys or something like this.

Thank you for the reading, will be very grateful for any help.


texts:
I'm currently up to try to improve the performance of some of my queries.
As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *
FROM table1
WHERE
    field1 NOT IN (
        SELECT
            index_field
        FROM table2
        WHERE
            user_id = '2'
    )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

Kind regards,

Dominik

//--- EDIT -->

The original query looked sth. like this

SELECT
    i.*
FROM stream_item si
LEFT JOIN user__publisher_item ui
    ON ui.user_id = 2
    && ui.item_id = si.item_id
INNER JOIN item i
    ON i.id = si.item_id
WHERE
    si.stream_id IN (5,7,8,9,19,24,29,42,43,44)
    && ui.id IS NULL || (
        ui.`read` = 0 || ui.saved = 1
    )
GROUP BY
    si.item_id
ORDER BY
    si.`found` DESC
LIMIT 0,10

I have a table which represents the n:m relationship between stream and items.
An unique item can be accessible via multiple streams.
Based upon this i have a state-table for user dependent states of an item.
Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table.
In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

//--- Edit 2 -->
The table-structure in the background is as follows:

stream
stream_item //n:m relation between stream and items
item //unique items
stream_group //user-defined groups of multiple streams
stream_group_streams //_n:m relation between stream_groups and streams_
user
user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

btw. sorry for my bad english


texts: Slave failing to replication in MariaDB 10

texts:
I am not able to enable row based replication. I am trying to set up a master slave replication between two nodes running.

Considerations

Server version        10.0.23-MariaDB-log
Protocol version  10
UNIX socket       /var/lib/mysql/mysql.sock.

The scenario I have followed is this:

Shut the prod (mc1), took a cold backup and restored on slave (mc2).
On master ran show master status; prodarchivedlogs-bin.000001 and the pos as 582240
Set these info in slave.
Started the slave, it started throwing up error message:

  160812  3:31:50 [ERROR] Slave SQL: Could not execute Update_rows_v1 event on table radius1.radacct; Can't find record in 'radacct', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log prodarchivedlogs-bin.000001, end_log_pos 587695, Gtid 0-1-79, Internal MariaDB error code: 1032

  160812  3:17:18 [Warning] Slave: Can't find record in 'radacct' Error_code: 1032

  160812  3:17:18 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'prodarchivedlogs-bin.000001' position 587072

I have tried all methods based on my knowledge, but it is just not functioning. I tried using mysqlbinlog tool to extract to a file and then tried inserting, even then it is failing.

The prod my.cnf is as follows

symbolic-links=0
innodb_buffer_pool_size=10G
innodb_data_home_dir= /var/lib/mysql/data2
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/redologs
innodb_log_file_size    = 256M
innodb_buffer_pool_size = 10000M
innodb_flush_method     = O_DIRECT
thread_stack    = 256K
max_connections=100000

expire_logs_days        = 5
max_binlog_size         = 100M

log-bin=/backup/archivedlogs/prodarchivedlogs-bin
binlog_format=row
server-id=1

The slave my.cnf is as follows

skip-name-resolve
skip-slave-start
datadir=/var/lib/mysql/data1
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

innodb_buffer_pool_size=10G
innodb_data_home_dir= /var/lib/mysql/data2
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/redologs
innodb_log_file_size    = 256M
innodb_buffer_pool_size = 10000M
innodb_flush_method     = O_DIRECT
thread_stack    = 256K
max_connections=100000
expire_logs_days        = 5
max_binlog_size         = 100M
log-bin=/backup/archivedlogs/prodarchivedlogs-bin
binlog_format=row
server-id=2

Further details

The master prod machine has 40 GB RAM and has a 16 core CPU.

I followed the process below:

created the rep user and gave the access.
shutdown prod.
scp'ied the files from prod to slave machine.
brought up master instance.
ran the command show master status\G;. took note of bing log and the postion.
brought up the slave instance
configured the slave profile with the bin log file and the position
ran the start slave command.
then ran the command show slave status\G; and I  started seeing the error messages of

  HA_ERR_KEY_NOT_FOUND.

Also:

Tried to run the query mysqlbinlog on the bin log. The report said the file is being used.
Ran the command flush logs; still no results.

Trying all methods to fix this.

Any help will be greatly appreciated.


texts: Creating a database for a scenario involving students, teachers and courses

texts:
I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup:

Models

Users(id, first name, last name, gender, password, email)
Roles(id, name)
Course(id, name, level, day, start time, end time)

Attempted relationships (might be wrong)

User 1->has->N Roles (I think I need a user/roles bridging table)
Students(user role 6) N->has(studies)->M Courses
Teachers(user role 3-5) N->has(teaches)->M Courses

Example data

Table: Courses

id name    level    teacher   day start_time end_time students (0NF)
--+-------+--------+---------+---+----------+--------+--------------
1  English Beginner 1         Mo  12:00      13:00    101,102...
2  English Beginner 1         Tu  12:00      13:00    101,124...
3  English Beginner 1         Fr  13:00      14:00    101,105...
4  English Expert   1         We  14:00      15:00    145,155...
5  Maths   Single   2         Mo  12:00      13:00    135,163...
6  Maths   Single   3         Tu  13:00      14:00    192,123...
7  Maths   Full     3         Tu  15:00      16:00    134,101...

Example report

Student                       (Id = 101)
-----------------------------

Attends English for Beginners (Taught by Teacher 1)
-----------------------------
Mon - 12:00 to 13:00
Tue - 12:00 to 13:00
Fri - 13:00 to 14:00

Attends Maths (Full)          (Taught by Teacher 3)
-----------------------------
Tue - 15:00 to 16:00

Please forgive me for any inconsistencies in structure - I am a beginner in DB design and DBA StackExchange also. Could I have the steps explained to arrive at a solution?


texts: what if mongos server goes down in shard cluster setup

texts:
I have followed the tuturial Deploy a Sharded Cluster

I have setup

config server replica set with 3 members (cfg1, cfg2, cfg3) [replica set name = configserverreplica]
mongodb server replica set with 3 members (mongoserver01, mongoserver02, mongoserver03)  [replica set name = mongodbreplicaset]
on another node named foreign-connector on which I am running mongos using command mongos --configdb "configserverreplica/cfg1:27019, cfg2:27019, cfg3:27019" --bind_ip_all —port 27020.
from client machine I connect to foreign-connector  with mongo --host foreign-connector --port 27020

Above is my set up.   And it works perfectly fine for failover test (I can take down any node at a given time... Everything works)

My question is what happens when mongos server (foreign-connector) goes down.
Is there any other way to be failover proof.

Thanks for your help is advance.


texts: Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

texts:
is there any effective way to tune the query below?

I have a filtered index on OrganisationID <> 0
Primary key is on another field, not relevant for this query ItemCode varchar(20).

I have tagged sql server 2005 because it needs to work there, but if there is anything else in any other sql server version, please bring it up.

--------------------------------------
-- START CLEAR
--------------------------------------

begin try
drop table #T
end try
begin catch
end catch

--------------------------------------
-- THE TABLE
--------------------------------------

CREATE TABLE #T
(
a1 int,
a2 int,
ID INT,
OrganisationID INT,
Distance INT,
constraint PKt1 PRIMARY KEY  CLUSTERED (A1,A2,ID)
)

--------------------------------------
-- ADDING SOME DATA
--------------------------------------

INSERT INTO #T
SELECT 1,1,0,10,100
UNION ALL
SELECT 1,1,1,10,200
UNION ALL
SELECT 1,1,3,10,50
UNION ALL
SELECT 1,1,4,20,80
UNION ALL
SELECT 1,2,5,20,300
UNION ALL
SELECT 1,2,6,0,100
UNION ALL
SELECT 1,3,7,0,100
UNION ALL
SELECT 1,3,4,10,100

GO
--------------------------------------
-- INDEX CREATION
--------------------------------------

create index idx_T_OrganisationID
on #T (id)
WHERE OrganisationID <> 0
GO

--------------------------------------
-- THE QUERY
--------------------------------------
--SET STATISTICS IO ON
--SET STATISTICS TIME ON

    SELECT
        PID.ID,
        PID.OrganisationID
    FROM #t AS PID
    WHERE id IN (
        SELECT MIN(id) FROM dbo.#t
        WHERE OrganisationID <> 0
        GROUP BY OrganisationID
    )

After Comprehensive Testing - The Results

out of these 3 queries:

    -- query 1

        SELECT
            PID.ID,
            PID.OrganisationID
        FROM #t AS PID
        WHERE id IN (
            SELECT MIN(id) FROM dbo.#t
            WHERE OrganisationID <> 0
            GROUP BY OrganisationID
        )

    -- query 2

        SELECT  rn.ID, --... other columns go here
            rn.OrganisationID
    FROM (
        SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id)
    FROM #t
    ) rn WHERE n= 1
           AND OrganisationID <> 0

    -- query 3
    SELECT OrganisationID, MIN(ID)
FROM #t T
WHERE OrganisationID <> 0
GROUP BY OrganisationID ;

the first one does not even bring the most correct results,
the query 3 (as suggested in the comments by  spaghettidba and  ypercubeᵀᴹ ) is the one with best performance in my live environment, with my real table and data, as you can see below, the query I had originally and the one based on query 3:

for this exercise in particular, using the table #T query 2 and query 3 perfom more of less equally as per the query plan below (picture):


texts: How to efficiently recompute the data in a rather large X table?

texts:
I have recently noticed performance issues for the main application I am developing right now (it is a legacy one). A job triggers a stored procedure that computes the security for all the documents and all the application users in the database. The table looks like the following:
Id INT IDENTITY(1, 1) CONSTRAINT PK_DocumentSecurity PRIMARY KEY CLUSTERED,
DocumentId INT CONSTRAINT FK_DocumentSecurity FOREIGN KEY REFERENCES Document,
UserId INT CONSTRAINT FK_DocumentSecurity FOREIGN KEY REFERENCES ApplicationUser,
CanWrite BIT NOT NULL,
+ an index for DocumentId and UserId

This table has about 3.6M records in production, but this keeps growing due to the increasing number of documents and users.
This happens very often (once a minute) and I have noticed using sp_who2 that it sometimes blocks other SPIDs. The profiler also indicates a very large number of reads and CPU, so clearly, this process is very heavy.
Sometimes, on the test environments, the computation seems to take forever, probably due to a very inefficient query plan that happens from time to time (test servers are significantly lower on resources than the production one).
I have checked the code and the current algorithm looks like the following:

compute a hash for all the records in the tables involved in the computation
if the hash is the same as before, do nothing
insert the result of a big and complicated SELECT statement (I will provide details below) to insert into a persistent table having the exact schema as above
when done the view used for joining to apply the security is altered to use the brand new computed table

The actual computation looks like the following (actual details are removed due to brevity):
SELECT DISTINCT ISNULL(ds.UserId, 0), ISNULL(ds.DocumentId, 0)
FROM (
    SELECT ... FROM Document ... CROSS JOIN ApplicationUser ...
    UNION
    SELECT ... FROM Document ... CROSS JOIN ApplicationUserRole ...
    UNION
    SELECT ... FROM Document ...
    UNION
    SELECT DISTINCT ... FROM DocumentDetail ...
    UNION
) ds
JOIN (
    SELECT ... FROM Document ...
    WHERE (... OR ... OR ... ) AND ...
    UNION
    SELECT ...
) dsp ON dsp.DocumentId = ds.DocumentId AND dsp.UserId = ds.UserId

From my perspective, there are two big problems with this way of computation:

maintenance: it is becoming harder and harder to introduce new security rules
performance: ORs and DISTINCTs are killing the performance

I have already created a story to refactor this code, but I would like to validate that my reasoning is correct (I have an OOP bias, as I am mainly a .NET developer) before starting working on it:

refactor the security rules (to be validated by the business, since they certainly look different than what is already there which also might not be 100% correct for all cases) that they all like the following (precedence matter here):

if role is admin, allow access for all documents if role is sales and
user has read role for document, insert with CanWrite = 0 if role is
sales and user has a write role for document, insert with CanWrite = 1

create a temporary table with the target schema

create an index for DocumentId and UserId

apply each security rule:
INSERT INTO #buffer (DocumentId, UserId, CanWrite)
SELECT ...
FROM Document D ...
  JOIN ApplicationUser U ...
WHERE NOT EXISTS (SELECT 1 FROM #buffer B WHERE B.DocumentId = D.DocumentId AND B.UserId = U.UserId)

truncate the target table

INSERT INTO DocumentSecurity SELECT * FROM #buffer

The last two steps might need to be optimized if they take too long (maybe replace with the swap the table under the view mechanism already in place).
This should make the code more maintainable and I assume the performance will be better if I remove all the ORs and the DISTINCTs, but I am interested in a DBA's perspective for such an approach. Is it suboptimal?
Note: I cannot have duplicates in the security table because there are currently many places (Entity Framework used in application layer + stored procedures) that simply JOIN with the security view (as opposed to check for existence).


texts: Should I place my Quartz Scheduler tables in a separate database?

texts:
I am designing a web application that uses Quartz Scheduler to scheduler jobs.

I have Quartz configured to use a JDBCJobStore, so it automatically stores each created job in its tables.

I am wondering if I should create a separate database to store these Quartz tables in, or if I should just store these tables in my web application's main database.

My application's database is not that complex, with only a couple of tables.

I should also note that Quartz allows you to specify a table name prefix for all of its tables, for instance "quartz_".  So if I did store these tables in my application's main database, they would be easily distinguishable from the other tables.


texts: How to count the number of times an attribute appears in an inner query?

texts:
I have this query that I am trying to write out:

  Which students (names) have taken more than one course from the same professor?

I have a students, course, and professor table. This is the ER diagram:

I have 4 tables:

Students (student_id is the PK)
Courses (course_id is the PK and professor_id is the FK to the Professor Table)
Takes_courses (student_id and course_id is the PK)
Professor (professor_id is the PK)

My plan:

Select student_name
From Student
Where student_id > Some (Select *
                         From Courses Natural Join Takes_Courses
                         Group By professor_id)

I am not able to run this query above. How am I supposed to count the number of student_id appears in the inner query?


texts: What does "Rows Removed by Index Recheck" without "lossy" mean?

texts:
Here's part of an EXPLAIN ANALYZE from Postgres 9.6:

->  Bitmap Heap Scan on cities  (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1)
       Recheck Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'Ã¤'::text, 'ae'::text), 'Ã¶'::text, 'oe'::text), 'Ã¼'::text, 'ue'::text), 'ÃŸ'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)
      Rows Removed by Index Recheck: 567
      Heap Blocks: exact=497
      ->  Bitmap Index Scan on city_lookup_index  (cost=0.00..89.98 rows=265 width=0) (actual time=4.229..4.229 rows=584 loops=1)
             Index Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'Ã¤'::text, 'ae'::text), 'Ã¶'::text, 'oe'::text), 'Ã¼'::text, 'ue'::text), 'ÃŸ'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)

What does Rows Removed by Index Recheck mean?

Or in other words: The bitmap is non-lossy (only exact pages), so why did it contain tuple pointers that were (presumably) removed by the recheck?


texts: restrict user host settings to socket connection only

texts:
Is there a way to restrict a user's host setting so that (s)he can only connect by socket and not TCP/IP? I'd like to have this setting for the root account.

edit: As Abdul Manaf pointed out skip-networking can be used to turn off TCP/IP connectivity altogether. But can it be done on a user account basis?


texts: Database created in sqlcmd but missing in SQL Server Management Studio

texts:
I created a database named testbase in sqlcmd in SERVER\MSSQLSERVER. However, I cannot access SERVER\MSSQLSERVER.

I also could not find testbase inside the server connection using Microsoft SQL Server Management Studio.

Appreciate any help / tips / hints.


texts: Performance impact of updating target columns with same values ON CONFLICT

texts:
This is a question about the inner-workings of Postgres (v10) and performance.

Given a table, github_repos, with a multi-column unique index on org_id and github_id columns, is there any performance difference (or other issues to be aware of) between the two bulk upsert operations below? The difference is that in the first query, the org_id and github_id columns are included in the UPDATE, whereas in the second query they are not. Since the UPDATE runs ON CONFLICT, the updated values of org_id and github_id will be the same as the old values, but those columns are included in the UPDATE because the underlying library I am using is designed that way. I'm wondering if its safe to use as-is or whether I should be explicitly excluding those columns in the UPDATE.

Query #1:

INSERT INTO "github_repos" ("org_id","github_id","name")
VALUES (1,1,'foo')
ON CONFLICT (org_id, github_id)
DO UPDATE SET "org_id"=EXCLUDED."org_id","github_id"=EXCLUDED."github_id","name"=EXCLUDED."name"
RETURNING "id"

Query #2:

INSERT INTO "github_repos" ("org_id","github_id","name")
VALUES (1,1,'foo')
ON CONFLICT (org_id, github_id)
DO UPDATE SET "name"=EXCLUDED."name"
RETURNING "id"

github_repos table:

      Column       |       Type        | Collation | Nullable
-------------------+-------------------+-----------+----------+
 id                | bigint            |           | not null |
 org_id            | bigint            |           | not null |
 github_id         | bigint            |           | not null |
 name              | character varying |           | not null |

Indexes:
    "github_repos_pkey" PRIMARY KEY, btree (id)
    "unique_repos" UNIQUE, btree (org_id, github_id)


texts: What is recommended level of security for a production database?

texts:
I have a production database on our local server. Currently there are two many logins to the database, about 20, half of them are windows authenticated and the other rest are SQL Server authenticated. I would really like to keep the logins minimum, so that I know who has accesses the database while also maintain security, but also want to keep safety factor, like keep one windows authenticated user so the we have access to the db in case no one has password. My question are

What is the recommended security level of production server? Should it be Windows authenticated only or SQL Server authenticated?
How many logins should one keep? one or more?
Should the default sa login be disabled, since it can be a security threat?

Any other security risk, I need to be aware of? Our application as well as database both sits on the same local server.


texts: No way to set maximum retrieved rows in pgadmin query tool

texts: I'm using version 1.16.1, and I can't find a "max(imum) rows" param to tweak.  Any help would be appreciated.


texts: Change host IP of master in mysql replication

texts: I have one master and one slave. Suppose IP of master is changed but the physical server is same. Now I want to update the new IP (of the master) at the slave server. At the slave server master_info_repository is set to TABLE. I want to resume the replication process from exactly the position where it has paused due to IP change. How to do this? mysql version of slave server is : 14.14 Distrib 5.7.30 and O/S is ubuntu 18.04.


texts: `show procedure status` doesn't list all of the procedures?

texts:
mysql-server-5.5.28-1.el5.remi

I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of them.

mysql> show procedure status where db='reportingdb';

...
| reportingdb | 7k_cronjob_log                             | PROCEDURE | user@host | 2012-10-17 10:10:55 | 2012-10-17 10:10:55 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-------------+--------------------------------------------+-----------+----------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
146 rows in set (0.05 sec)

As you can see 7k_cronjob_log is the last procedure and there is only 146 rows. But let's see what happens:

mysql> show create procedure reportingdb.warningserver_getmaxtime_v2\G
*************************** 1. row ***************************
           Procedure: warningserver_getmaxtime_v2
            sql_mode: NO_AUTO_VALUE_ON_ZERO
    Create Procedure: CREATE DEFINER=`user`@`host` PROCEDURE `warningserver_getmaxtime_v2`()
    READS SQL DATA
    DETERMINISTIC
BEGIN
    SELECT `LastTimeCalc` FROM `000_sys_params_v3`;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Any thoughts?


texts: MySQL Permissions Necessary To Reset Password

texts:
On my team there is a desire for some users to periodically update their MySQL password.

Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm correct, changing your password requires access to the mysql.user table (which would, in turn allow the user to modify the password of any other user on the server.)

If this is all correct, is there any way to secure the database in a way that a user can update their password, but not other users?


texts: Cannot login oracle database using other users

texts: I'm currently using oracle xe c18.  I can login using sqlplus / AS SYSDBA as oracle user.  However, the command does not work for other users.  It always shows an error ORA-01017: invalid username/password; logon denied.  I already give SUID and GUID to all installation files.  What's the problem?


texts: How do I run a large script with many inserts without running out of memory?

texts:
Question:

I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run?

Context:

Added some new data fields to make an app play nice with another app
the client uses.
Got a spreadsheet of data from the client full of
data that mapped current data items to values for these new fields.
Converted spreadsheet to insert statements.
If I only run some of the statements it works but the entire script does not.
No. There are no typos.

If there is a different way I should be loading this data feel free to chastise me and let me know.


texts:
i am attempting to run a subquery in a table:

SELECT t1.*, t2.* FROM t1
LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 on t1.col1 = t2.col1
WHERE ...

My main question is, since that subquery called on it's own would pull the entire time, does it also pull the entire time while it's in a subquery, even though the WHERE condition on the parent query is only pulling just a few rows of the actual database?

I suppose I could put the where query inside the subquery, but its a pretty complex where query that involves other tables.


texts: How can I identify when to create a new table to hold data that can be obtained from a query?

texts:
We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when figuring out the commission rate the agent gets, but nothing obscenely complex.

For example, it will probably never be more complex than this:

SELECT Payments.Amount * CASE
    WHEN DateDiff(year, Client.Received, Payments.DatePaid) = 1 THEN Rates.Rate1
    WHEN DateDiff(year, Client.Received, Payments.DatePaid) = 2 THEN Rates.Rate2
    ELSE Rates.Rate3 END

Would it make sense to build a 2nd table to hold this data instead of querying for it anytime it's needed? Or should I just stick with run-time queries that pull the data whenever it's requested?

And more importantly, what are the factors to use when determining if a query should be run anytime the data is needed, or if the data should be stored in a separate table of it's own?


texts: ORA-01031: insufficient privileges solution oracle

texts:
Create or replace procedure myprocedure
is
   stmt varchar2(1000);
   stmt2 varchar2(1000);
begin
    stmt := 'create global temporary table temp(id number(10))';
    execute immediate stmt;
    stmt2 := 'insert into temp(id) values (10)';
    execute immediate stmt2;
end;

when i execute the procedure

exec myprocedure;

i got the error:

ORA-01031: insufficient privileges
ORA-06512: at "I3.MYPROCEDURE", line 7
ORA-06512: at line 2


texts: RDS or Object DB for 99.99% static data of 25 million rows in one table

texts:
I have important data that consists of approx 25 million rows and will grow year on year. This dates back to 1995 and is split into yearly CSV files.

Example:  1995.csv, 1996.csv and so on up to 2016.csv.

The data is of UK addresses with the Postcode being the primary identifier.

Let's say I perform a search on SW19 2EA.

I need the database to query the data and return the result set that is available for that given postcode. This will return data that contains information such as the address, postcode, property type and so on.

The problem I have at the moment is querying 25 million rows in a single table is fairly heavy and has a hefty delay. That being said, I'm only just learning advanced SQL and my configuration is probably not up to scratch.

We have 2x 128 GB RAM servers both running dual Xeon x5's on 10 Gbit dedicated connections. We assume the severs are of no issue whatsoever, and therefore are unsure what the best solution would be.

Do we store all the databases into a single table and do some research on the best configurations or do we store them in an object database?

The data will not change apart from new data being added in 2017 that is for the 2017 year. All other years are static and are never changed.

My initial thought was to store each data set in its own table according to each CSV file, so, for example, 1 table for 1995 data, 1 table for 1996 data and so on. The question is, whenever a search query is performed, is it possible to have multiple workers query each table at the same time, so that, let's say, 10 tables are being queried concurrently as opposed to one giant 25-million-row table?

Could you use the same use case on an object database? I'm also curious if it's possible to cache the entire database, it being static data. On the other hand, would this significantly reduce the query delay of around 20 seconds that I currently experience on the single 25-million-row table?

Example of CSV data structure:

"3E0330EF-67CA-8D89-E050-A8C062052140","112000","2006-05-22 00:00","MK13 7QS","F","N","L","HOME RIDINGS HOUSE","13","FLINTERGILL COURT","HEELANDS","MILTON KEYNES","MILTON KEYNES","MILTON KEYNES","A","A"

I have been using Laravel with MariaDB backend and been importing the CSV files into MariaDB. Then I have been simply using a query like this:

Select * from history where `postcode` = 'MK13 7QS';

and in Laravel terms:

$postcode = 'MK13 7QS';
History::where('postcode', '=', $postcode)->get();

Database Layout is not perfectly set up and the data types can certainly be tweaked to make them more ideal for the dataset.

Currently I have just used varchar on every column apart from the date column, as I have only been playing about with things to try and get a more suitable setup and configuration working for production.

I'm hoping to get some much appreciated advice from a newcomer point of view.


texts: Are foreign keys important while restoring database

texts: I dumped out a MySQL database with foreign keys. I want to restore the database, but the restore database is used only for read access. Do I need to index the foreign keys?


texts: Can PostgreSQL support integration test with some kind of throwaway overlay?

texts:
It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself.

I know that I could wrap my test in a transaction and rollback the transaction after the test run. But it would be very nice if PostgreSQL could provide some kind of global snapshoting or throwaway overlay. In an ideal case such a feature would cover all state of the database including schemas and stored procedures.


texts: How do I optimise XPath Querying over a large dataset?

texts:
I have a table that has 220 M rows. Also the table has a xml column that has XML data. I have configured single file for data and another ndf for index. Table's primary key is guid column. When am querying the XML contents using xpath query and load the flatten XML data to another table it takes around 30 hrs to load the data. Since am in standard edtition, partition is not an option. Need some advice on same
1. how to improve data load performance.
2. I have a non-clustered index configured on the table which will be rebuilt after load.


texts: Read from partition in local cassandra node only

texts:
I'm aware that I can read from any cassandra node and it acts as coordinator to read from the node containing a specific partition, but can I read data only from the partition which is on the node I'm connecting to?

In other words, when a cassandra cluster has 10 nodes, it contains 10 partitions on each node (and maybe replicas on other nodes when RF is set). When I send a SELECT * FROM TABLE I would like to get only 1/10th of the total data, which is really stored on that specific node without any traffic to other nodes.

Thank you so much!


texts: How to retrieve master log position and master log file in shell script

texts:
I'm writing a shell script for point in time recovery for MySQL database.For the below statement I need to specify the start position and end position of a log file.

mysqlbinlog --start-position=368315 --stop-position=368316 /usr/local/mysql/data/mysql-bin.123456

How can I retrieve the log position and log file.


texts: Comparing two tables for a UUID change and fix it

texts:
I have two tables which I'm trying to reconcile the differences of in postgresql.

Table A is old and needs updating.

Table B is an updated, schema identical version of Table A which I have the data for in a temporary table in the database of Table A.

Unfortunately some time after the two databases diverged someone changed the UUIDs of records in table B and I need table A to match table B.

The schema for both tables is:

CREATE TABLE A
(
    uuid VARCHAR(36) NOT NULL,
    template_folder_uuid_parent VARCHAR(36),
    heading VARCHAR(255) NOT NULL,
    image VARCHAR(100),
    downloaded BOOL NOT NULL,
    allow_in TEXT NOT NULL,
    template_group VARCHAR(255) NOT NULL,
    country_filter VARCHAR(1024) NOT NULL,
    user_id INT,
    date_created TIMESTAMP,
    date_modified TIMESTAMP,
    is_modified BOOL NOT NULL,
    image_path VARCHAR(255)
);

I need to search through Table A and Table B and match records based on template_folder_uuid_parent and heading, then set the UUID of the Table A record to the UUID from Table B. Once changed in Table A the UUID will cascade correctly.


texts: PostgreSQL predicate `IS OF` and `IS NOT OF` supported or documented?

texts:
Reviewing some of the regression tests for PostgreSQL, I can see

SELECT x, x IS OF (text) AS is_text FROM q;

This apparently dates back to 7.3

  Add IS OF / IS NOT OF type predicate (Thomas)

Is this form supported. In my own code, I would have written,

pg_typeof(x) = 'text'

But I like IS OF better.


texts:
I'm trying to archive something like this

The MongoDB instance on dc1.com is currently a standalone, and On-Site periodically pulls a backup via mongodump over a ssh tunnel.

I now want to create two instances, one On-Site and one in a second datacenter, and make the three instances become a replica set, so that the backup is almost instant. This way I would have an On-Site backup and one in the second datacenter, which would always be up to date.

If the main MongoDB instance in dc1.com fails, then that's it, no data can be served, this will then need to get fixed manually, as the On-Site instance and the one in the secondary datacenter should not take over. Nor are those two meant to be queried. They are there just as a backup.

My problem is that when I add --replSet to the main database, and then a rs.initialize(), that this instance is then somehow not recognized as bound to localhost, but to 172.17.0.1, which is the docker interface, which I need to bind to so that the containers can connect to MongoDB. If I then try to add a member via rs.add("localhost:2001"), which is the instance on dc2.com, I get an error about not being able to mix localhost and non-localhost databases. The main instance is recognized as 172.17.0.1 instead as localhost.

  "Either all host names in a replica set configuration must be
  localhost references, or none must be; found 1 out of 2"

I then used https://stackoverflow.com/questions/28843496/cant-initiate-replica-set-in-ubuntu in order to issue a rs.initiate({_id:"rs0", members: [{"_id":1, "host":"127.0.0.1:1000"}]}) to force the initialization of the replica set on dc1.com to be bound to localhost. But when I then try to add a member via rs.add("localhost:2001") I still am not able to do it. It's no longer the hostname error, but another one, which I can't quite remember, as I got tired of trying and had to rollback everything. It was something like connection rejected. When I do a mongo --port 2001 I get disconnected after a warning about something related to "isMaster".

Is it possible to create this kind of setup? All I'm trying to do is to avoid using TLS on MongoDB and binding the backups to globally accessible interfaces (the On-Site instance would be behind a firewall, so 0.0.0.0:1002 would not be publicly accessible)


texts: Oracle: Viewing settings for DBMS_NETWORK_ACL_ADMIN ACL?

texts:
How do I view the contents of a network ACL? For example, if I create this ACL, how can I view what settings have been applied to it?

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
     acl => 'www.xml',
     description => 'WWW ACL',
     principal => 'SCOTT',
     is_grant => true,
     privilege => 'connect'
  );


texts: What controls the precision when writing reals in postgres copy command?


texts: DB2 equivalent for MySQL's DROP .. IF EXISTS

texts:
Is there an equivalent in DB2 for MySQL's (or PostgreSQL's):

DROP TABLE IF EXISTS sometable;


texts: Backup/restore of a database that has a filestream

texts:
I have a SQL DB to which I have recently added Filestream data.  Now we have a daily backup/restore to a QA server, and since I added the Filestream data, this has started failing.  So I modified the "restore" statement to include a "with move":

RESTORE DATABASE MyDb
FROM DISK = 'c:\somepath\mydb.bak'
WITH RECOVERY, REPLACE,
MOVE 'MyDb' TO 'f:\sql\MyDb.mdf',
MOVE 'MyDb_Log' TO 'f:\sql\MyBb_log.ldf',
MOVE 'MyDb_Filestream' TO 'f:\sql\MyDb_FS', NOUNLOAD, STATS = 10;

But this is failing with the following error:

  File 'MyDb_Filestream' cannot be restored to 'f:\sql\MyDb_FS'. Use WITH MOVE to identify a valid location for the file.

I've Googled all over to find a solution for this.  This answer says that you need 3 separate RESTORE DATABASE statements, but doesn't spell out what they are.  The accepted answer here indicates that you need to copy over the whole Filestream folder manually, which I find very difficult to believe.  And in any case it doesn't make clear what the process is.

Can anyone please spell out to me, in idiot-proof, step-by-step instructions, how exactly one is supposed to backup a DB containing a filestream, copy it to another server and restore it there?

Thanks!


texts:
What usually causes this mysqldump error?

exec error: Error: Command failed: mysqldump: Got error: 1066: Not unique table/alias: 'TableFoo' when doing LOCK TABLES


texts: Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

texts:
I have a table with below structure and it's data :
create table test_table
(
Item_index   int,
Item_name    varchar(50)
)

insert into test_table (Item_index,Item_name) values (0,'A')
insert into test_table (Item_index,Item_name) values (1,'B')
insert into test_table (Item_index,Item_name) values (0,'C')
insert into test_table (Item_index,Item_name) values (1,'D')
insert into test_table (Item_index,Item_name) values (0,'E')

I want to know why changing the column in order by section of the query , changes the result? In QUERY-1 , I used item_index and in the QUERY-2 I used item_name column in the order by section. I thought that both queries must generate the same result because I used item_index in both queries for partitioning! I'm completely confused now ! why the order by column should affect the final result?
QUERY-1:
select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_index) new_column
from test_table t;

RESULT:
Item_index  Item_name     new_column
----------- --------------------------
0           A                E
0           C                E
0           E                E
1           D                D
1           B                D

QUERY-2:
select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_name) new_column
from test_table t;

RESULT:
Item_index  Item_name  new_column
----------- -----------------------
0           A             A
0           C             C
0           E             E
1           B             B
1           D             D

Can anybody explain how exactly these two queries are being executed and why each of them generates different result?
Thanks in advance


texts: Online migration using Database Migration Services

texts:
I wanted to test online migration to azure sql db using azure database migration services.  I am following the below link.
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online

But in step 7 under "Create Migration project", I am unable to see online data migration under activity.  I have DMS in Central US location.

Anybody had the same issue?


texts: How Can I Ignore Partition Schemes in my VS SSDT Database Project

texts:
We use SSDT in VS 2015 to manage our database project code.  Recently, we've been implementing partitioning on some tables in databases that are managed with SSDT.

I need to include the partition function and partition scheme definition in my project if I want to declare a dependency on that scheme in my table definitions.  I'd prefer to ignore changes to the scheme and function so we can manage that outside of the project.

The problem I am having is that SSDT's publish profile appears to not be honoring my partition scheme exclusion settings even though they appear to be set correctly.

When I go to Advanced options in the publish profile settings and click on the Ignore tab, I have "Ignore partition schemes" and "Ignore table options" checked in the top box as well as "Exclude partition schemes" and "Exclude partition functions" checked in the bottom box.

Unchecking the option for partition functions appears to be behaving properly.  When checked, the function disappears from the generated deployment script.  However, regardless of which options I check and uncheck in regards to the partition scheme the partition scheme still appears in the publish script.

This is a screenshot of my settings:

I recently upgraded from SSDT v14.0.61707.300 to v14.0.61712.050 but the behavior still exists (the latest release 17.4 on the MS site) https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

Can anyone explain what I'm doing wrong or is this a confirmed bug?

Is the partition scheme forced to exist in the publish script under certain circumstances that my project may be affected by?

I'd rather not have to completely remove all partitioning definitions from the project in order to work around this single issue as it will obscure the definitions.


texts: Get a specific number of rows from parent table?

texts:
I have a parent table, foo, and a child table, bar, in a mySQL database.

Each row in foo may have zero or more child records in bar.

I want to get exactly 15 rows from the parent table, and all their child records. I initially did:

SELECT <columns>
FROM foo
LEFT JOIN bar
    ON foo.a = bar.a
WHERE <condition>
ORDER BY <condition>
LIMIT 15;

However, this will give me 15 joined rows. It's possible that the last row retrieved is a foo with multiple bar's, and that this query doesn't get all of the bar's. My requirement is that if a foo is in the result set, all of the bar's associated with that foo have to be returned as well.

What's the most efficient way to write a query that will get me 15 foo's with all of their bar's? I can think of a couple of ways of doing this, but none of them seem to be terribly elegant. (Doing a select on the parent table and then doing a second query with a join using WHERE <foo.id> IN ..., for example.) I'm not a DBA so I figured I'd ask the pros here.


texts: Oracle: unable to change to noarchivelog

texts:
We discovered that our Oracle 12c database was taking up a large amount of disk space with archive logs, so I figured out how to delete the files, but I'm unable to disable archive logging.  We don't have an in-house DBA, so I'm trying to figure this out myself.  From lots of Googling, it's my understanding that I need to do the following:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

I've got two problems though.  I can't do the "sqlplus / as sysdba", it complains about "ORA-12560: TNS:protocol adapter error" unless I specify the SID, even though it the environment variable is defined.  Also, when I do the shutdown, I get "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" and it disconnects me from Oracle.

C:\Users\administrator.MYDOMAIN>set oracle_sid=orcm

C:\Users\administrator.MYDOMAIN>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 5 11:11:33 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

SQL> connect sys/password@orcm as sysdba
Connected.
SQL>
SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\app\oracle\product\12.1.0\dbhome_1\RDBMS
Oldest online log sequence     6869
Next log sequence to archive   6871
Current log sequence           6871
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Warning: You are no longer connected to ORACLE.
SQL>

Any assistance would be greatly appreciated.


texts: Unable to call PASSWORD() with VARCHAR() parameter inside a procedure

texts:
DELIMITER //
CREATE PROCEDURE custUpdateInfo(IN customer_id varchar(20), IN passwordd VARCHAR(20), IN named varchar(20), IN addressd VARCHAR(60), IN phone_number DECIMAL(10) UNSIGNED, IN email_id VARCHAR(20))
BEGIN
    IF (CHAR_LENGTH(passwordd) > 0) THEN
      UPDATE Users SET Users.passcode = passwordd WHERE Users.username = customer_id;
    END IF;
    IF (CHAR_LENGTH(passwordd) > 0) THEN
      set password for customer_id = PASSWORD('hi');
    END IF;
    IF (CHAR_LENGTH(named) > 0) THEN
      UPDATE customer SET customer.name = named WHERE customer.customer_id = customer_id;
    END IF;
    IF (CHAR_LENGTH(addressd) > 0) THEN
      UPDATE customer SET customer.address = addressd WHERE customer.customer_id = customer_id;
    END IF;
    IF (CHAR_LENGTH(phone_number) > 0) THEN
      UPDATE customer SET customer.phone_number = phone_number WHERE customer.customer_id = customer_id;
    END IF;
    IF (CHAR_LENGTH(email_id) > 0) THEN
      UPDATE customer SET customer.email_id = email_id WHERE customer.customer_id = customer_id;
    END IF;
END;
//
DELIMITER ;

This code works, but the following doesn't:

DELIMITER //
CREATE PROCEDURE custUpdateInfo(IN customer_id varchar(20), IN passwordd VARCHAR(20), IN named varchar(20), IN addressd VARCHAR(60), IN phone_number DECIMAL(10) UNSIGNED, IN email_id VARCHAR(20))
BEGIN
    IF (CHAR_LENGTH(passwordd) > 0) THEN
      UPDATE Users SET Users.passcode = passwordd WHERE Users.username = customer_id;
    END IF;
    IF (CHAR_LENGTH(passwordd) > 0) THEN
      set password for customer_id = PASSWORD(passwordd);
    END IF;
    IF (CHAR_LENGTH(named) > 0) THEN
      UPDATE customer SET customer.name = named WHERE customer.customer_id = customer_id;
    END IF;
    IF (CHAR_LENGTH(addressd) > 0) THEN
      UPDATE customer SET customer.address = addressd WHERE customer.customer_id = customer_id;
    END IF;
    IF (CHAR_LENGTH(phone_number) > 0) THEN
      UPDATE customer SET customer.phone_number = phone_number WHERE customer.customer_id = customer_id;
    END IF;
    IF (CHAR_LENGTH(email_id) > 0) THEN
      UPDATE customer SET customer.email_id = email_id WHERE customer.customer_id = customer_id;
    END IF;
END;
//
DELIMITER ;

The difference is in line

set password for customer_id = PASSWORD(passwordd);

which was originally

set password for customer_id = PASSWORD('hi');

The error I get is:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'passwordd);
      END IF;
      IF (CHAR_LENGTH(named) > 0) THEN
        UPDATE custom' at line 7


texts:
here is the situation I fu*d up my mysql setup, before I did that I copied entire /var/lib/mysql directory to another location. Now fresh installation of mysql is back and I cannot restore my files.

If I simply drop files back to /var/lib/mysql, things get wired, for example I can see table names from mysql cli, when I try show tables but when I try to access a table I get message

  Table '*' doesn't exist

Now this problems seems pretty common, I have searched all over Google and I understand that we cannot move InnoDB files just like that. There is InnoDB table counter and other other mystical stuff for me.

But one thing I cannot understand is I have all files that should contain all information about database, I'm talking about mysql/ directory inside /var/lib/, why is it so hard to recover database from that, and also is there any place other than /var/lib/mysql/ where InnoDB keeps information about database?


texts: Migrate replication from SQL Server 2008 R2 to SQL Server 2016 - Old to new hardware

texts:
I will be working on a project shortly that would involve moving the replicated databases off of old to the new SQL Server instance. I haven't done this in the past so I don't have much knowledge either on how to move ahead except to do some research.

As of this date our current config sits on a Windows 2008 R2 server with SQL Server 2008 R2 and publisher along with the distributor is the same SQL Server instance e.g sqla and the subscriber is sqlb and also now sqlb is acting as a publisher and distributor for sqlc... these are all pull subscribers with one way transactional and snapshot replication.

Now, back to my original question. Is there a way to migrate the databases off of the old to the new with least downtime so I don't have to script out and reinitialize subscriptions as it would take a lot of time (that's what most of the people mentioned on google). Do we have any hacks or tricks to make this work without causing problems or if there is any step by step to work it out even if we use script method.


texts: I can't find documentation about whether agent jobs in SQL Server can be backed up. If not, how can I back them up in order to restore during the future recovery in case of an instance failure?


texts: MySQL: Process was running fast on DigitalOcean after moving it to my local machine it runs slower

texts:
I have a Symfony project in which I crawl rss feeds and collect articles. I used to have it hosted on Digital Ocean with a droplet instance of 4 cores and 8GB RAM.
In the biggest table I have around 500k rows, on Digital Ocean the MySQL instance was running pretty ok handling the crawl processes (between 11 and 20 parallel crawl processes).
Due to the fact that the disk space was filled on my droplet (it reached 44GB on my biggest table) I decided to take another approach and move the whole project on my local machine.

DO Instance:

4 vCPUs
8GB RAM
50GB DiskSpace (don't think it's important)
Ubuntu Linux Server 18.04

Local machine:

8 Core CPU (Intel Core i7 7700)
32 GB RAM
2TB HDD (mysql data folder is held on this hard disk)
128GB SSD - os is installed on this
Ubuntu Linux Desktop 18.04

Is there a difference between MySQL versions installed on desktop and server ubuntu versions?
My DO instance CPU usage (htop) was almost always approaching 100% on all 4 CPUs, on my local machine the CPU usage is around 20% on each core while running 20 crawl processes at a time.
I might be missing a certain configuration on my local machine?
Running a simple UPDATE query on an InnoDB table that is not used by the project but is in the same database takes forever (I've been waiting for 10 minutes now almost). Running a SELECT (over through ssh tunneling) on the same table runs smoothly.
All tables in my project are InnoDB.
Having a look at mytop I have around 20 SELECT queries and 4 update queries right now.

The DO instance was running both a webserver and the mysql instance and it handled everything well from my point of view. On my local machine at this point I'm using the webserver only on my internal network.

Can't figure it out, can anyone give me a hint?

On the DO instance I didn't do any special settings to MySQL and neither to my local machine other than changing the connection_timeout to 24hours + 1 minute (I needed it in another project where I had a process sleep for 24 hours and needed the connection still available when resuming the process).

mysql -V returns:

mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper


texts: Create an explain plan for query over a global temporary table

texts:
I have a query which aggregates data from a global temporary table. The table will be filled right before the aggregation. My goal is a reliable explain plan for that query.

Is there a way in OracleDBMS to simulate a fill level of the temporary table, witout the necessity to insert anything?

Example

Normal query:

INSERT INTO gtt VALUES(..)
SELECT * FROM gtt

Explain plan:

EXPLAIN PLAN FOR SELECT * FROM gtt /* with hint for maybe 10k records */

Motivation: The goal is to find out whether the statement falls below the threshold values for costs and CPU. We want to find out, what kind or what amount of temporary data breaks our tuning concept.


texts:
I have this function ( It counts how many characters I have in a certain string, separated by ,:

ALTER FUNCTION [dbo].[fContaRespostas]
(
 @List VARCHAR(6000),@SplitOn VARCHAR(5),@Resposta VARCHAR(50)
)
returns integer
AS
BEGIN
Declare @Qtd int
Set @Qtd = 0

Declare @Value varchar(255)
Set @Value = ''

WHILE (Charindex(@SplitOn,@List)>0)
 BEGIN

    Set @Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    IF ( @Value = @Resposta)
        begin
            Set @Qtd = @Qtd + 1
        end
    SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

 END

 return @Qtd
END

This is the test. It needs to display 8, But it shows 7.

select dbo.fContaRespostas('5,5,5,5,55,5,5,5,5',',','5') as Qtd

If i use , in the end, it counts as 8 5.

select dbo.fContaRespostas('5,5,5,5,55,5,5,5,5,',',','5') as Qtd

What's wrong in the code? I really can't find it.


texts: How present the result table below the last result query table, SSMS 2017

texts:
os: window 10
SSMS 2017, v17.9.1

I watched youtube videos of SSMS running two different queries and the last result was displayed below the last table.
Example that I am referring in youtube.
Now when I run a new query It does not conserve the last table there, I only can see the last result table.
I want to see the last two or three tables there in the same windows to compare them, Is it possible?
Thanks in advance.


texts:
I want to download MySQL and use it on CentOS. But I found the downloading selection menu on the MySQL website is confusing. Which one should I choose? See the attached image.

Thanks.


texts: How to update a sequence column using single update statement

texts:
I have a table, lets call it foo which consist a column priority and has a unique constraint on it.

lets assume below is the current state of foo table :-

mysql> select * from foo;
+-----+----------+
| id  | priority |
+-----+----------+
| 1   |        5 |
| 2   |        6 |
| 3   |        7 |
+-----+----------+

Now I have a requirement where,if I insert a another row with priority value of 6, then it should insert this row and move priority of all the rows which has its value >= to 6 to 1 priority down.

So revised table should look like below :-

mysql> select * from foo;
    +-----+----------+
    | id  | priority |
    +-----+----------+
    | 1   |        5 |
    | 2   |        7 |
    | 3   |        8 |
    | 4   |        6 |
    +-----+----------+

I want to use a sql statement like

update foo set priority=priority+1 where priority >=6; But obviously it will fail with below error :-

  ERROR 1062 (23000): Duplicate entry '7' for key 'priority'.

Is there is any other way of doing the same thing.


texts: How to uninstall Oracle 11G software that are installed in Windows 8?

texts:
How to uninstall Oracle 11g software that are installed in Windows 8?
I have installed oracle 11g version in local machine.


texts: Switching context between two instances from one account

texts:
I have a server with two instances running on it (a dev environment) where we use one login to access either instance.  I wanted to be able to switch context from one instance to the other without changing my .profile to reference the other instance's db2profile, logging out and back in.  I wrote a script (iswap) to allow me to do that.  Here's what it looks like:

  if [[ $DB2INSTANCE = "instance1" ]]
  then
     . /usr/users/instance2/sqllib/db2profile
     echo "Switched context from instance1 to instance2"
  elif [[ $DB2INSTANCE = "instance2" ]]
  then
     . /usr/users/instance1/sqllib/db2profile
     echo "Switched context from instance2 to instance1"
  fi

If I do a "db2 list db directory" after running iswap it shows me the databases in the new instance.  If I echo $DB2INSTANCE it reflects the new instance.  But, if I do a "db2 list applications" it still shows me the applications connected to the original instance.   What am I not changing that I need to add to the script?


texts: How to add up a column depending on data from another column

texts:
I have a table which has results and a table that has band_name and band_id.

Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I have

SELECT
  SUM(placing.points) AS sumpoints,
  band.band_id
FROM placing, band
GROUP BY band.band_id
ORDER BY SUM(placing.points) DESC

The results end up giving me each unique band_id but the total of all the points on all bands as oppose to the bands points.


texts: Threshold for sort spill and random cardinality estimates

texts:
I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014:

USE tempdb

IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest

CREATE TABLE tblTest
(
          c1 INT         PRIMARY KEY CLUSTERED,
          c2 INT        ,
          c3 CHAR (1000)
);

GO
SET NOCOUNT ON;

BEGIN TRANSACTION;

DECLARE @i AS INT;

SET @i = 1;

WHILE @i <= 10000
          BEGIN
                    INSERT  INTO tblTest (c1, c2, c3)
                    VALUES              (@i, @i, 'a');
                    SET @i = @i + 1;
          END

COMMIT TRANSACTION;

GO
UPDATE STATISTICS dbo.tblTest
GO
SET STATISTICS XML ON;
GO
--no tempdb spill (SQL Server 2014)
--in sql server 2012 always different "Estimated number of rows" if you run the whole script several times
SELECT   *
FROM     tblTest
WHERE    c1 <= 5948
ORDER BY c2
OPTION (MAXDOP 1);

GO
SET STATISTICS XML OFF;

SET STATISTICS XML ON;
GO
--no tempdb spill (SQL Server 2014)
SELECT   *
FROM     tblTest
WHERE    c1 <= 5949
ORDER BY c2
OPTION (MAXDOP 1);

GO
SET STATISTICS XML OFF;

(core of the query was based on one material from MS)

1) The first thing I am curious about is what causes the tempdb spill during sort operation at this specific level of c1 column. All the estimates are correct and count of the read page is the same for both queries. So why is the second query spilled? (In another words why memory grant for the later query is so much higher).

2) I have tested this query on SQL Server 2012 and got very interesting behaviour as well. Firstly I was unable to get to the same thresholds, so I run the script repeatedly and noticed that estimated number of rows is always different than it was in the previous run. My question is why is the estimated number of returned rows always different when I repeatedly run the very same script (creating, inserting a updating it's own statistics via full scan)?


texts: Transform SELECT query to DELETE to remove extra taxonomy entries

texts:
I try this query shows me the products that have duplicated, term_taxonomy_id in my web corresponds the stock of the product
(none, medium, high, low)

I would like to transform it into one query DELETE... that will delete those that have repeated and leave only the most current by the term_order, because if the product have two stock tag or more is not displayed on the web.

They only have to have a tag

object_id=1 have high,none
object_id=3 have none, medium*

table: wp_term_relationships

+-----------+------------------+-----------+
| object_id | term_taxonomy_id | term_order|
+-----------+------------------+-----------+
| 1         | high             | 0         |
| 1         | none             | 1         |<---delete none
| 1         | bbb              | 10        |
| 1         | ccc              | 10        |
| 2         | high             | 0         |
| 2         | aaa              | 11        |
| 2         | bbb              | 11        |
| 2         | ccc              | 11        |
| 3         | none             | 0         |
| 3         | medium           | 1         |<---delete medium
| 3         | high             | 12        |<---delete high
| 4         | jjj              | 12        |
| 5         | kkk              | 12        |
| 5         | lll              | 12        |
| 5         | high             | 12        |
| *         | *                | *         |
+-----------+------------------+-----------+

SELECT object_id,term_taxonomy_id,term_order
FROM wp_term_relationships
WHERE (    term_taxonomy_id = none
        OR term_taxonomy_id = medium
        OR term_taxonomy_id = high
        OR term_taxonomy_id = low)
GROUP BY object_id
HAVING COUNT(*)>1

It can only have one type of taxonomy related to stock, but there can be many more taxonomies. That's why I put aaa,bbb,ccc,zzz the only exclusive ones are those related to high,medium,low,none stock.

I want to delete all the records related to the stock (high,medium,low,none) just leave one for the lowest order of completion and leave all the items values that are not related to the stock.

My platform is MySQL 5.7 (can't use except)


texts: Postgres: how to limit the number of query results by aggregate type

texts:
Consider the following table definition

CREATE TABLE associations (
    id SERIAL PRIMARY KEY,
    resource_id integer,
    target_id integer,
    relation_id integer
);

which is used for specifying the association of a resource to a target by means of a relation:

296 10  49  4 <--
297 10  50  4 <--
298 10  51  4 <--
299 10  52  4
300 10  53  4
301 10  54  4
302 10  55  4
303 10  56  5 <--
304 10  57  5 <--
305 10  58  7
306 10  59  7
307 10  60  7
308 10  61  7
309 10  62  7
310 10  64  8 <--
316 11  80  8 <--
320 11  81  8 <--
321 11  82  8 <--
322 11  83  8

resource 10 is associated to targets [49, 50, 51, 52, 53, 54, 55] by relation 4
resource 10 is also associated to targets [56, 57] by relation 5

How can one query the associations table to get the targets of resources for specific relations (e.g. 4, 5 and 8) while limiting the number of returned targets per relation?

For example if we were to limit the number of targets to 3 on relations 4, 5 and 8 we should obtain the following result:

10 | 49, 50, 51 | 4
10 |     56, 57 | 5
10 |         64 | 8
11 | 80, 81, 82 | 8

Thank you.


texts: PostgreSQL - how to list all UNIQUE indexes for specific database?

texts:
I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this.

I found these two queries.

SELECT indexname FROM pg_indexes;
SELECT * from pg_indexes where schemaname = 'public';

but neither the one, suits my needs.
I only need just those which identify indexes for unique columns.
Thanks.


texts: MS SQL Server 2008: How to improve performance after deleting transaction log file

texts:
Due to the size of transaction log file (it grew up to 200 GB) I had to delete it because the disk was running out of space. I know this is not a good practice but anyway after that the performance is very bad.

Does anyone know whether it is possible to improve performace and how in this situation?


texts: Stored Procedure RAISERROR - Transaction Count Msg Returned

texts:
I have a stored procedure that should return the below RAISERROR message when called. However, the text is appended to the message when returned:

Transaction count after EXECUTE indicates a mismatching number of
BEGIN and COMMIT statements. Previous count = 1, current count = 0

Why is this? Is the statement order in the CATCH block correct?

SET XACT_ABORT ON; -- ensures the entire transaction is terminated and rolled back. **https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

BEGIN TRY
BEGIN TRAN --all as one transaction.

    IF EXISTS(
            SELECT
                SupervisorID
            FROM [dbo].[UserDetail]
            WHERE
                SupervisorID IS NOT NULL
            )
    BEGIN
        RAISERROR('Unable to alter a approved data.', 18, 1)
    END
COMMIT TRAN
END TRY
BEGIN CATCH
    -- Determine if an error occurred.
    IF @@TRANCOUNT > 0
        ROLLBACK;

    -- Return the error information.
    DECLARE
        @error_number AS INT,
        @error_message AS NVARCHAR(1000),
        @error_severity AS INT;
    SELECT
        @error_number=ERROR_NUMBER(),
        @error_message=ERROR_MESSAGE(),
        @error_severity=ERROR_SEVERITY();
    RAISERROR(@error_message,@error_severity,1);

END CATCH