texts: Disable indexes on Amazon RDS PostgreSQL instance

texts:
If I correctly understand according to this advice: https://stackoverflow.com/questions/6146024/is-it-possible-to-temporarily-disable-an-index-in-postgres

it is possible to temporary disable indexes in all catalogue in order to increase performance of huge number of insert operations:

update pg_index
set indisready = false
<where ... >

But there is a problem: I run my instance on Amazon RDS and I have user granted access only to my particular schema. It is not granted to perform updates on pg_index table. Other user is rdsadmin which is internal Amazon's system user (according to this explanation: https://serverfault.com/questions/587695/what-is-the-rdsadmin-user-and-should-i-care ).

Are any other ways increase speed of huge number of inserts operation or disable indexes in this case?


texts: SQL-Server - Grant user DBO permission on database

texts:
I have a reporting database that I restore every night from a backup of the production database.
When I restore, I need to add a user to the DBO role for that database.

I thought this would work:

use Restored_Prod
go
exec sp_addrolemember 'db_owner', 'chris'
go

But I get the following error, since that user doesn't exist in the Restored_Prod database:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'chris' does not exist in this database.

How do I add the user 'chris' to the database to be able to run the sp_addrolemember to make him a DBO of that database?
Is there a way to add the user permissions using sp_addrolemember command from the master db where the user exists?


texts: Mysql replication dump without down time? empty sql file

texts:
I try to create dump as replication master data and use followed command
 to create dump, but sql file only contains paramters and there is no data and structure.
The command is :

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > bckp.sql.gz

Contents of sql file is:
-- MySQL dump 10.13  Distrib 5.1.41, for debian-linux-gnu (x86_64)
--
-- Host: xxx.xxx.xxx.xxx    Database: XXX
-- ------------------------------------------------------
-- Server version       5.1.72-0ubuntu0.10.04.1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-12-26  8:56:27

Why does this problem occur?


texts: I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there are recommendations to disabled TCP Chimney Offloading, RSS and NetDMA. So i disabled those features but still the error is recurring. What possibly causes the failure? Thanks


texts: MySQL installation broken after Windows crash - how to repair MySQL and recover data?

texts:
recently my PC powered off abruptly for no apparent reason, leaving my Windows 7 installation severly damaged. I can still log onto the machine, but tons of Windows system files have been corrupted and I have no other choice than performing a repair install of Windows.

At the same time, my MySQL 5.5.31 instance running on this system has been damaged. I am not sure if it's just the MySQL installation that got damaged, or the data itself. What I can say is that while the serve was certainly running during the shutdown, it did not perform any tasks at that time.

What happens now is that when I try to run mysqldump from MySQL Workbench, it aborts after a couple of tables saying that it has lost the connection to the server.

Most tables are InnoDB, a few MyISAM.

How can I repair the installation and recover my data? Should I uninstall and re-install?

Appreciate your help.

EDIT: These are the two errors I get from the error event log in MySQL Workbench:
"InnoDB: Page checksum 3072680322, prior-to-4.0.14-form checksum 1925954319
InnoDB: stored checksum 24388168, prior-to-4.0.14-form stored checksum 1077079361
InnoDB: Page lsn 3225980244 611338381, low 4 bytes of lsn at page end 2304517124
InnoDB: Page number (if stored to page already) 2370756659,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 4293596229"

"InnoDB: Assertion failure in thread 3988 in file page0page.ic line 745
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery."


texts: how to perform regular backups of SQL Express 2008 R2 databases

texts:
Which instruments to choose to implement backup strategy on 150 servers with SQL Servers Express Edition 2008 R2? The strategy should also include checkdb, index and statistics maintenance. In case of errors there should be some kind of notifications. Each of the machines is turned off every night.

Allowed data loss - 15 minutes.


texts:
Please help..

I have website running on XAMPP Apache and MySQL (v3.1.0) with Windows Server 2003, it works fine for almost 5 year. I often restart the MySQL service. But today when I stopped the MySQL service and tried to start, it nothing happen, MySQL service still stopped.

When I saw the log, it seems failed to initialize buffer pool. Below snippet of the log

171214 10:13:06 [Note] Plugin 'FEDERATED' is disabled.
171214 10:13:06 InnoDB: The InnoDB memory heap is disabled
171214 10:13:06 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171214 10:13:06 InnoDB: Compressed tables use zlib 1.2.3
171214 10:13:06 InnoDB: Initializing buffer pool, size = 171214 10:14:28 [Note] Plugin 'FEDERATED' is disabled.
171214 10:14:28 InnoDB: The InnoDB memory heap is disabled
171214 10:14:28 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171214 10:14:28 InnoDB: Compressed tables use zlib 1.2.3
171214 10:14:28 InnoDB: Initializing buffer pool, size = 171214 10:17:32 [Note] Plugin 'FEDERATED' is disabled.
171214 10:17:32 InnoDB: The InnoDB memory heap is disabled
171214 10:17:32 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171214 10:17:32 InnoDB: Compressed tables use zlib 1.2.3
171214 10:17:32 InnoDB: Initializing buffer pool, size = 171214 10:23:24 [Note] Plugin 'FEDERATED' is disabled.

Below are snippet log for success mysql startup

171213 13:39:47 [Note] Plugin 'FEDERATED' is disabled.
171213 13:39:47 InnoDB: The InnoDB memory heap is disabled
171213 13:39:47 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171213 13:39:47 InnoDB: Compressed tables use zlib 1.2.3
171213 13:39:47 InnoDB: Initializing buffer pool, size = 1.0G
171213 13:39:48 InnoDB: Completed initialization of buffer pool
171213 13:39:48 InnoDB: highest supported file format is Barracuda.
171213 13:39:49  InnoDB: Waiting for the background threads to start
171213 13:39:50 InnoDB: 1.1.8 started; log sequence number 197337651912
171213 13:39:50 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171213 13:39:50 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171213 13:39:50 [Note] Server socket created on IP: '0.0.0.0'.
171213 13:39:50 [Note] Event Scheduler: Loaded 0 events
171213 13:39:50 [Note] d:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.27-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

(from Comment)

[mysqld]
port    = 3306
socket  = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 500
key_buffer = 256M
table_cache = 256M
slow-query-log=1
slow-query-log-file=D:\TABLETEMP\my.slow.log
long_query_time=1
log_error="mysql_error.log"


texts: Re-map DB Users/Logins after Restore WITH STANDBY

texts:
This is related to my previous post..

Differential Backup Issue - Why? Is this Possible?

Basically, the issue which I am having now is that in my restore process (as part of ETL), there are tasks to re-map db users to logins (because they are SQL Auth users in server A so lose mapping when restored on server B). Since restore with STANDBY will not allow this as it will be read-only db.. how can I get around this..?


texts: Sybase server build error, missing library files

texts:
I'm trying to build sybase ase server through sample resource file and i got this error. "error loading shared libraries: libXm.so.4: cannot open
shared object file: No such file or directory". So ran ldd on srvbuild and found below, two of them are missing.. any idea how to resolve this?

The OS is RHEL6.5 64bit and its ASE 16, express edition.

environment variables set.


texts: Will subquery be executed first here with full scan?

texts:
I have below query

select *
    from employee employee,
    (select address1.id
        from address1 add1, address2 add2
        where add1.address2_id = add2.id ) address_data
    where emp.empId = 100
    and emp.address_id = address_data.id
// index exists  on both columns i.e. address_id and id

I was expecting first subquery(address_data) will be executed internally and there will be full scan on address1 and address2 table. Then outer
query will be executed.

Revised understanding after looking at explain plain

But when I looked at explain plain looks like it is not happening. I found there was not any full scan on address1 and address2 table. Looks like
first outer query executed(which returned only record) and then only relevant records were fetched from address1 and address2 table(just fyi address tabl also returned 1 row).

This strategy looks logical to me as there is no need to do full access of address tables in this case.

My question - Is my revised understanding correct ? But I was under impression that sub query will always be executed first. Look like  I was wrong here ?


texts:
I am running a security RAG on reporting server that updates our security and put database to read_only mode. I am using wildcard to skip system and some other databases, but what can I use to skip any database that's a part of AG. in other words what can I use to skip synchronized DB?

Thank you.


texts: job send email to developers upon complexion and include DBAs if it fails

texts:
I have a job that sends an email to an operator (which contains a list of emails within it) upon complexion.

Some of these people that are on that list are the developers who build everything in that job.

I am the DBA but I am also in the list.

QUESTION:

Is there a way to make this job send an email to those guys upon complexion, and to the DBAs only if the job fails?

So, upon complexion of the job, which has 4 steps,
the developers will always get an email, (the job is scheduled to run every day),
and if (by any reason) the job fails, the developers AND  the DBAs will get and email.


texts:
What is the significance of DB CPU paramater in Automatic Workload Repository? Say in my case it's always above 100. Does it have benchmark metric as such to say that so and so number is the right value? How does it work?

Thanks!!


texts: How to resolve Error 600122, severity 11, state 1 was raised

texts:
My SQL Server 2008 R2 keeps recording these error in the SQL Server log:

  Error 600122, severity 11, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Some are:

  Error 400006, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

I also got similar errors in server event log as follow:

  Error 600122, severity 11, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Are they with regard to compatibility level of Databases as https://www.experts-exchange.com/questions/28523045/Error-55555-severity-16-state-1-was-raised-but-no-message-with-that-error-number-was-found-in-sys-messages-If-error-is-larger-than-50000-make-sure-the-user-defined-message-is-added-using-sp-addm.html said?

Is there anybody resolved it? Because it's in my Prod environment so I want to get more info.

Thanks a lot!


texts: Running scheduled SSIS pkg with SQL Server 2012 Web Edition

texts:
I have a client with SQL Server 2012 Web Edition. I believe it has SQL Server Agent, but I see on the following Microsoft page that many integration services are not supported:

https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx

My guess is that I could develop an SSIS solution and perhaps run it scheduled by calling it from DTEXEC and scheduling that as a job in the Server Agent.

I agree that getting the client on Standard Edition would be best, but if that is not do-able, can they still run a scheduled SSIS solution scheduled? (Solution is an incremental load from flat files)


texts: Expand sparse table with self outer join on distinct values

texts:
I have this:

g1       | g2        | x
---------+-----------+-----
New York | Monday    | 210
New York | Tuesday   | 258
Chicago  | Monday    | 30
Chicago  | Wednesday | 25
LA       | Thursday  | 40

But I want this:

g1       | g2        | x
---------+-----------+-----
New York | Monday    | 210
New York | Tuesday   | 258
New York | Wednesday | NULL
New York | Thursday  | NULL
Chicago  | Monday    | 30
Chicago  | Tuesday   | NULL
Chicago  | Wednesday | 25
Chicago  | Thursday  | NULL
LA       | Monday    | NULL
LA       | Tuesday   | NULL
LA       | Wednesday | NULL
LA       | Thursday  | 40

In other words, I want a record for every combination of DISTINCT g1 and DISTINCT g2. I should note that g1 and g2 are arbitrary groupings (not always location- or date-related). How do I fill in the table with these NULL values based on the cross product?


texts:
Well, I have no exact information to remotely set up my connection to 5gb free. I have already added my IP address to remote_connection settings.
They have provided this info only.

database name = username_dbname
database user = username_dbuser
host = localhost
port = default
database password = dbpassword


texts: What would be the easiest way to create database of packaging of package in warehousing database?

texts:
I am creating a warehouse application for a client where goods are received in boxes.
The problem is, they contain more boxes in it.
Like if I have a product say Apples, then they receive 5 Boxes of Apples each containing 3 boxes in it. So in all there are 15 small boxes of apples.
Now, the question is how to simplify this task in my database?
Like I want bags in bags, box in bags, bags in box, etc etc.
This is critical because, again I have to code algorithm for remaining stock, current stock and returned stock too.


texts: Memory continually being eaten up since I had SQL Server 2014 SSMS installed

texts:
I know SQL Server will take up all memory on a server by design, but this is my desktop not a network server.  What should I be looking at in terms of what they installed that needs to be changed so that I can work again?

I am running both SQL Server 2014 and SQl server 2008 SSMS as well as SSIS packages for both. I have no SSMS open and no are there programs besides this and task Manager Lync and my system is up to 94% of my memory being used just after a reboot. I never had this issue before they loaded 2014. But it has been continual since they did. I am sure there is some setting I need to change, but can't find anything on it as all the things I find seem to refer to how memory is handled on a server. It currently is running SQLServer.exe even though I have not opened SSMS since the reboot.

I have 6 GB of memory on an Intel Xeon CPU and Windows 7. It is an HP Z600 workstation.


texts: SELECTing an optional column, and doing SUM on it

texts:
I have two tables (from Request Tracker 4, actually): Tickets, and Transactions.

Tickets.id == Transactions.ObjectID

but there aren't always Transactions for every Ticket.

I'm interested in the Ticket's "TimeWorked" column. This is often set on Ticket Creation, but it's also sometimes updated by a Transaction which updates "TimeWorked". I want to get the TimeWorked value as it originally was which means I think I have to backtrack through all the linked Transactions and subtract their values. Subsequently, I also want all the updated Transactions which modified that value, but I believe I've solved that problem.

Basically I want to see who set the TimeWorked value when, and what it was on ticket creation. Here's my SQL:

SELECT
Tickets.id                                                            AS Ticket,
Tickets.Subject                                                       AS Subject,
/*
SUM(Transactions.NewValue - Transactions.OldValue)                    AS TransactionTimeWorked,
Tickets.TimeWorked-SUM(Transactions.NewValue - Transactions.OldValue) AS InitialTimeWorked,
*/
Tickets.Created                                                       AS TicketCreated
FROM
    Tickets
         LEFT JOIN Transactions       ON Tickets.id=Transactions.ObjectId
            AND Transactions.Field="TimeWorked"
WHERE
    Tickets.Queue = 50
    AND Tickets.Created > "2015-06-01"
    AND Tickets.Created < "2015-09-01";

Including that first SUM line limits the number of rows returned to those Tickets which have updating Transactions. I sort of understand why, but I want all Tickets to be included, with their original create time calculated by SUMming the Transactions and subtracting, but only if they exist.

I think my WHERE clause is correct in making the JOIN values optional, but now I think I want the same on the SUM.

An example of the output I'd like:

+--------+---------+-----------------------+-------------------+---------------------+---------------------+
| Ticket | Subject | TransactionTimeWorked | InitialTimeWorked | TicketCreated       | TransactionCreated  |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+
|  11234 | test01  |                  NULL |                60 | 2015-08-03 08:23:35 |                NULL |
|  11234 | test01  |                    20 |              NULL | 2015-08-03 08:23:35 | 2015-08-04 08:23:35 |
|  11234 | test01  |                    10 |              NULL | 2015-08-03 08:23:35 | 2015-08-06 15:34:35 |
|  11235 | test02  |                  NULL |                30 | 2015-07-31 12:50:37 |                NULL |
|  11236 | test03  |                  NULL |                20 | 2015-07-31 14:51:29 |                NULL |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+


texts: Integration Service cannot be upgraded/removed

texts:
I have SQL Server 2005 Integration Services installed in our server whose version is 5292. It needs to be upgraded to version 5324. After running the patch the summary report says it is upgraded successfully. But the version is still 5292 when I connect with SSMS. For compliance purposes, I tried to remove it, but that failed unfortunately. The report says the server is lacking the IIS component. That put me in a quandary.

Now I think removing the service might be easier than upgrading it.

Have you encountered anything similar? How can I force removal of Integration Services?


texts:
Hello guys i have worked on mysqli but not pdo sqlite i don't know what syntax is to create tables here if not exists this is my code please tell me why am i getting error on last line.

$Database = new PDO('sqlite:'.$DB_Path);
$Database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$Database->exec(
        "CREATE TABLE IF NOT EXISTS Requests
          (
            ID INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
            Sender VARCHAR( 225 ),
            Recipient VARCHAR( 225 )
          )
      ");


texts:
Structure

CREATE TABLE `template` (
    `name` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `valid` DATE NULL DEFAULT NULL,
    UNIQUE INDEX `name` (`name`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Data

I am trying to select right template name based on date. "Valid" column represents date till template is valid. If Template have 0000-00-00 or NULL valid date, that means that this template is current (latest). Template names does not suggest order (they can be random string).

Scenario:

If I have user, that will be created let's say 2020-01-01, then select should pick template with name "yes_4".

Scenario:

User was created in 2018-01-01, then template selected should have name "agreement_x"

Scenario:

User created in 2019-01-15 will select row with name "template_2"

So far:

:date = - user created

query

SELECT * FROM `template`
WHERE DATE(`valid`) < DATE(:date)
ORDER BY DATE(`valid`) DESC
LIMIT 1


texts: Replicate single table from Postgres 9.3 replica

texts:
I have an existing Postgres 9.3 server acting as a read-only replica using the built-in streaming replication.

I need to replicate a single table from a database on that server to another physical server.

I've tried using bucardo but it doesn't like talking to read-only databases.

Is there a way to trigger an update on the remote server from the replica?


texts:
I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really Appreciate the help .

I'm using sqlite .json extension is not available.what I'm trying to do is::group all the rows having listing_id as a single value(23,27...).is there any other way?


texts: PostgreSQL function not executed when called from inside CTE

texts:
Just hoping to confirm my observation and get an explanation about why this is happening.

I have a function defined as:

CREATE OR REPLACE FUNCTION "public"."__post_users_id_coin" ("coins" integer, "userid" integer) RETURNS TABLE (id integer) AS '
UPDATE
users
SET
coin = coin + coins
WHERE
userid = users.id
RETURNING
users.id' LANGUAGE "sql" COST 100 ROWS 1000
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

When I call this function from a CTE, it executes the SQL command but does not trigger the function, for example:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
1 -- Select 1 but update not performed

On the other hand, if I call the function from a CTE and then select the result of the CTE (or call the function directly without CTE) it executes the SQL command and does trigger the function, for example:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
*
FROM
test -- Select result and update performed

or

SELECT * FROM __post_users_id_coin(10,1)

Since I don't really care about the result of the function (just need it to perform the update) is there any way of getting this to work without selecting the result of the CTE?


texts: POSTGRESQL: Refresh the materialized view which effect when there are change based on columns

texts:
I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works fine. Can someone help me to make a function so that the trigger only refresh the affected matview based on changes in the column?
for example table_1 has columns a and b. table 2 has columns c and d.
matview_1 takes columns a and c, matview_2 takes columns a and d, matview_3 takes columns b and c. I need if there is a changes in a row of table 1 column a, only matview_1 and matview_2 is refresh by the store procedure trigger. but if there are changes in row of table_1 column b, only matview_3 is refresh.

table_1
a  |  b
"""""""
   |

table_2
c  |  d
"""""""
   |

matview_1
a  |  c
"""""""
   |

matview_2
a  |  d
"""""""
   |

matview_3
b  |  c
"""""""
   |

Here my function trigger for all table:
CREATE OR REPLACE FUNCTION sp_refresh_matview() RETURNS trigger AS
$$
DECLARE
rec RECORD;
BEGIN
    FOR rec IN
        SELECT *
        FROM pg_matviews WHERE (
            definition LIKE '%'||TG_TABLE_NAME||'%')
    LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW '|| rec.matviewname;
    END LOOP;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

Here my create trigger:
CREATE TRIGGER trigger_tabel AFTER
INSERT OR UPDATE OR DELETE ON name_table
FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview();


texts: I'd like to produce a listing of linked servers and their mapped logins.  Anyone know how?  sys.sysservers will show servers, but no login information is presented.


texts:
How to find out how much i/o resources a task, session, request or transaction is currently using?

by task I mean the DMV called DMV sys.dm_os_tasks.
by session I mean the DMV sys.dm_exec_sessions

Sometimes a session has a request, by request I mean the DMV sys.dm_exec_requests
or a session might have a transaction instead - sys.dm_tran_database_transactions

How can I link these things with I/O usage?

I had a look at the DMV called DMV sys.dm_os_tasks .

The DMV sys.dm_os_tasks has some interesting columns to find out more about processes currently running in sql server.

sys.dm_os_tasks - not showing pending_io_byte_count

However, when I try to identify how much I/O each of my tasks is dealing with the pending I/O in bytes is always zero.

This is an example of the results of my query while I was trying to monitor some CXPACKETS WAITS.

Pending_IO_count seems to be fine, but pending_IO_by_counts are always zero.

what could be an alternative?
I want to measure the I/O usage of each parallel process.

in fact it could be just the I/O usage of each session or request.

even sessions without request sometimes are good to see, because they might be blocking other processes.


texts: Possible to have duplicate fully qualified schema names with separate data in DB2 for z/OS?

texts:
We have several application teams that are looking to do a re-write of large legacy applications.

This is mostly COBOL going against DB2 for z/OS (v11).

The COBOL code was generated using a tool, so we don't have any access to the source. Let's say the SQL cannot be changed for the sake of this question.

They would like environments (dev/test/perf) where they can copy the existing data and test the re-written application (still using the same SQL) while still having a team supporting the legacy system through dev->test->perf->prod.

Current SQL example:

SELECT FOO FROM MYAPP.BAR;

Because the table name was fully qualified with "MYAPP" instead of supplying it later, they cannot simply copy the code that they already have within the same DB2 and just change the schema name. They would like entirely new DB2 subsystems to avoid this issue.

Is there any way to avoid this? Are there any bind parameters or other strategies I could employ to have duplicate schema names running on the same DB2 subsystem?


texts: pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

texts:
I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib
9.1.11 having initially database clusture in directory /var/lib/postgresql/9.1/main.

By using initdb command i have created two other database clusters under directories /usr/local/pgsql/data and /usr/local/pgsql/standby under port 5433 and 5434 respectively. Changes in postgresql.conf and pg_hba.conf files of both clusters as follows:

In postgresql.conf:

wal_level = hot_standby
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_statement = 'all'

In pg_hba.conf:

local   all             all                                    trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5
local   replication     postgres                                trust
host    replication     postgres        127.0.0.1/32            trust
host    replication     postgres        ::1/128                 trust

After that I have installed pgpool to by command sudo apt-get install pgpool2.
This installation create  directory in /etc/pgpool containing files as follows

pgpool.conf
pool_hba.conf
pcp.conf

Changes pgpool.conf as follows:

listen_addresses = '*'
port = 9999
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'localhost'
backend_port0 = 5434
backend_weight0 = 1
backend_data_directory0 = '/var/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

pool_hba.conf file as follows:

local   all         all                               trust
host    all         all         127.0.0.1/32          trust

Make entry in pcp.conf of postgresql user with md5 password.
restart pgpool  /etc/init.d/pgpool2 start command and it started.

But when i am trying to use command psql -p 9999 then it throws error as follows:

  bpsql: ERROR:  MD5 authentication is unsupported in replication, master-slave
  and parallel modes.
  HINT:  check pg_hba.conf**.

I have lot of googlee but I am not getting any solution.
Please help me to find solution and suggest me if I am doing anything wrong.


texts:
I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.

My SQL:

SELECT
    *
FROM
    company_employee_contacts
WHERE
    corporate_complaint_type_ids = ANY(ARRAY[1,3]::integer[])

But it is giving me below error:

  ERROR:  operator does not exist: integer[] = integer

Can anyone tell me why I am getting this error while I am typecasting it?

Expected Result should be like:

id   | corporate_complaint_type_ids
----------------------------------
3212 | {1,3}
3216 | {1}
3218 | {3}
3220 | {1,2,3,4}
3221 | {3,4,5}


texts: Containment check tstzrange @> timestamptz not using btree or gist index

texts:
Schema:

        Column        |           Type
----------------------+--------------------------
 id                   | integer
 event_id             | integer
 started_at           | timestamp with time zone
 ended_at             | timestamp with time zone
 created_at           | timestamp with time zone
    "event_seat_state_lookup_pkey" PRIMARY KEY, btree (id)
    "event_seating_lookup_created_at_idx" btree (created_at)
    "event_seating_lookup_created_at_idx2" gist (created_at)

Query:

SELECT id
FROM event_seating_lookup esl1
WHERE
  tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)') @> esl1.created_at;

Explain analyze:

Table with <100k rows.

 Seq Scan on event_seating_lookup esl1  (cost=0.00..1550.30 rows=148 width=4) (actual time=0.013..19.956 rows=29103 loops=1)
   Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
   Buffers: shared hit=809
 Planning Time: 0.110 ms
 Execution Time: 21.942 ms

Table with 1M+ rows:

Seq Scan on event_seating_lookup esl1  (cost=10000000000.00..10000042152.75 rows=5832 width=4) (actual time=0.009..621.895 rows=1166413 loops=1)
  Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
  Buffers: shared hit=12995
Planning Time: 0.092 ms
Execution Time: 697.927 ms

I have tried:

VACUUM FULL event_seating_lookup;
VACUUM event_seating_lookup;
VACUUM ANALYZE event_seating_lookup;
SET enable_seqscan = OFF;

Problem:

event_seating_lookup_created_at_idx or event_seating_lookup_created_at_idx2 indexes are not being used.

Notes:

PostgreSQL 11.1.
btree_gist extension is installed.
I have tried equivalent setup with created_at timestamp without time zone and using tsrange; same result.
I understand that rewriting the query with >=, < checks would make it use the btree index. The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work.


texts: Which mysql collation is good for storing unicode strings?

texts:
For all I know it must be utf8mb4_unicode_ci, but:

> create table t1 (f1 varchar(255) collate utf8mb4_unicode_ci)
> insert into t1 (f1) values ('')

And what I get is:

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

The symbol I'm inserting into the database is this one.


texts: MySQL 5.6 replication causes 'waiting for table lock'

texts:
All of the sudden queries on slave server stopped with status "Waiting for table level lock"

I restart mysql service and stop replications and locking does not show up anymore. Once I turn replications back on I see huge increase in "waiting for table level lock" status for queries (show full processlist)

Replication is crucial for our situation and we can't keep it turned off.

What might cause this problem? Replication was running fine for last 5 months or so.

MySQL 5.6


texts: Does a database backup include all files?

texts:
If I have two files in my database (mdf & ndf), when I do a backup of the database with the statement:

backup database mydb;

are both the two files are backed up?


texts: I have a table which have a * to * relationship with another table. Lets say the first table is T1 and the second table is T2. So I need to keep the T2 Pks linked with single row in T1. I really like to go with 1NF here and add new table to keep the relationship. The number of rows linked from T2 is around 10 at maximum. The PK of T2 is a bigint.  So doing this will require me to either join which will be not really good because of dplicates that will come from T1. Second is to do a seperate query from application to get the T2 PKs. Now if I go with option 2 every thing is nice but I require an additional query. Then I though why I can't keep the T2 PKs in a T1 column delimited. This will avoid a query that is require just to find the T2 ids. Well this is feasible since there never will be a requirement to query T1 values by T2 PKs.  Any other good solution for this? Or should still stick with 1NF to solve this.


texts: Is it possible to restore sql-server bak and shrink the log at the same time?

texts: We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it needs 100GB to be restored. I believe this is because there database is set up to have a 75GB transaction log size. After restoring the database we can shrink the log file but is there a way to do this in the restore?


texts:
I have a database that is used for reporting services. This database was created by a contractor who is no longer around. Buried all throughout the database are references to a single table on a linked server. The content on that linked server is being moved, and the server being retired.

Rather than hunting around for all of the linked server references and updating them to point at the new linked server, or changing them to use a synonym name, would it be possible (or acceptable) to create the new linked server, then add a synonym that uses the same name as the current fully qualified linked server reference, but points to the new linked server? This would allow me to leave all database code intact and minimize disruption to business users.

Here's what I'm thinking. Current linked server is SRAPP. All queries using the linked server point to OPub.dbo.ADE.

 exec sp_addlinkedserver @server=SQLProd1

Then

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

Finally,

exec sp_dropserver 'SRAPP'

Will SQL Server get confused by a synonym name that looks identical to a linked server reference?


texts: What data type are the numbers in a SQL Server statistics object?

texts:
Specifically I'm most interested in:

The 'All density' number in the density vector
Numbers in the histogram

E.g. are they floats / decimals? What precision / scale / length do they have?

The motivation for this is that I think I might be seeing rounding errors, so would like to know how precisely we should expect cardinality estimations to match with these stats.

I'd be happy for an answer for any version of SQL Server, but 2012 and 2014 are most relevant to me.


texts:
I can find the latest Postgres versions such as 11.1 as source code configured to be downloaded and built for FreeBSD amongst the Ports Collection.

I cannot find compiled binaries for the latest versions of Postgres amongst the FreeBSD Packages.

pkg search postgresql

…returns items for Postgres 9.3 through 9.6, and version 10.5. But I see no entries for Postgres versions 11 or 11.1.

➥ Is this a new normal, not pre-compiling Postgres 11 for FreeBSD? Is compiling from the Ports Collection the usual way to install Postgres on BSD?

➥ Am I looking in the wrong place or in the wrong way?


texts: How to query database tables structures for DATE NOT NULL DEFAULT '0000-00-00' and ALTER them in MySQL

texts:
I am using MySQL v.5.7.x.
In a database with 161 tables, one of the them has the following structure:

CREATE TABLE `prefix_coupon` (
`coupon_id` int (11) NOT NULL AUTO_INCREMENT,
`name` varchar (128) NOT NULL,
`code` varchar (10) NOT NULL,
`type` char (1) NOT NULL,
`discount 'decimal (15,4) NOT NULL,
`logged` tinyint (1) NOT NULL,
`shipping` tinyint (1) NOT NULL,
`total` decimal (15,4) NOT NULL,
`date_start` date NOT NULL DEFAULT '0000-00-00',
`date_end` date NOT NULL DEFAULT '0000-00-00',
`uses_total` int (11) NOT NULL,
`uses_customer` varchar (11) NOT NULL,
`status` tinyint (1) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`coupon_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8

See the columns date_start, date_end and their structure date NOT NULL DEFAULT '0000-00-00'?

Is there a way to query database tables for

date NOT NULL DEFAULT '0000-00-00'

and ALTER them

ALTER TABLE prefix_coupon CHANGE date_start date_start DATE NULL DEFAULT NULL, CHANGE date_end date_end DATE NULL DEFAULT NULL;


texts: Frequently Querying the DMV - sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests

texts:
Could frequently (e.g. 5 seconds) querying the below DMVs from a custom tool to get the connection info impact performance on SQL server? I understand this would give a snapshot at that point.
SQL audit logs probably the best way to get this info but It would help if I get any guidance based on your experience with the DMV.

select sys.dm_exec_sessions.session_id, host_name as [Connecting machine name], client_net_address AS [Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, name as DatabaseName,
sys.dm_exec_sessions.status AS [Session status],command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,sys.dm_exec_sessions.status,r.status, text, *
from sys.dm_exec_sessions inner join sys.dm_exec_connections on sys.dm_exec_sessions.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_requests r on sys.dm_exec_connections.connection_id = r.connection_id
inner join sys.databases on r.database_id = sys.databases.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle)


texts: Is my subquery inneficient. It is extremely slow

texts:
I have two tables. Member information and login entries. I am simply trying to display the member information table sorted by the last login access date.  So i created this query, but it is extremely slow. Is there a better way to write it?

SELECT
    menmembers.memb_id,
    menmembers.firstname,
    menmembers.lastname,
    (SELECT Max(trank.datetr) AS MaxOfdatetr
        FROM trank
        where trank.cod_usr=menmembers.memb_id;) AS LastLogin
FROM menmembers;


texts: how to get only those records that have more than one values?


texts: Newbie question: database for a language school

texts: I'm building a database to create lessons for an online language school. I'm not an expert but this is what I came up with. Each lesson will be composed by "blocks" that can be pretty much anything (language presentation, listening, etc) that can contain some media. I would like to know if at a first look it makes sense or there is something that looks out of place. Thank you!


texts:
I have a table of Orders from Customers in my database for a retail store (using My SQL) and I am trying to do a ROLL UP of all orders for the month of April and for the final row to have a title of 'Grand Total' next to this total sum figure:

SELECT coalesce (OrderID, 'GrandTotal') AS OrderID,
SUM(OrderPrice) as SumOrderPrice
FROM ORDERS
WHERE OrderDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY OrderPrice WITH ROLLUP;

The problem I am having is that when I run this code, next to the total 'Sum' figure, I am getting a duplicate OrderID number, not the text 'Grand Total'. Any ideas where I'm going wrong?
Many thanks


texts:
We all know that index rebuilding creates lot of log growth..so what changes would you consider to your logshipping side to accomodate this log growth?

Do we increase the frequency of the logbackups?
ship the logs to an external storage such as SAN?

Please give your valuable suggestions.


texts: Meaning of "nonclustered located on primary"

texts:
When I run sp_help (using ALT+F1) for a table, on the indexes, there is a column index_description. and the rows have for example:

nonclustered located on PRIMARY (on foreign keys)
nonclustered, hypothetical located on PRIMARY (on non clustered indexes I created)
clustered, unique, primary key located on PRIMARY

I am more interested in the located on PRIMARY part, What does it mean?


texts: Could not add Replica Set to MongoDB Shard

texts:
I want to deploy a MongoDB Cluster for testing on 3 Virtual Machines using Docker.

On the first Virtual Machine I have the Router Instance and a Replica Set of the 3 Config Servers.

The second Virtual Machine is hosting a Replica Set of three MongoDB Server. They where successfully initiated and elected a Primary.

The third Virtual Machine is hosting a Replica Set of three MongoDB Server. They where successfully initiated and elected an Primary.

When I start my Container which contains the Router it connects successfully to the Replicaset of the Config Server.

If I now want to add my 2 Replica Sets as Shards with the following Command:

sh.addShard("rep1/rep1-node1:27018")

rep1 is the name of the Replica Set. rep1-node1 is the hostname for the Container with the Primary Node. (Host Name resolution works fine.)

After a short time I get the Following Error:

{
    "code" : 133,
    "ok" : 0,
    "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rep1"
}

I Have found another question here with the same topic but I don't get any errors with missing Authentication Key. None of my Mongo Instance uses any authentication because it's only a testing environment.

Adding normal Mongo Servers works without any problems. Only Replicasets won't work. I'm using MongoDB version 3.4.


texts: MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

texts:
In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY).

Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.


texts: Issues with mysqldump after upgrade 5.5 -> 5.6

texts:
A few months ago I updated a MySQL setup from 5.5 to 5.6. Since then I've been having problems with a script that I use to dump the various databases so that I can back them up.

The script is a short piece of perl that gets a list of all databases and then calls mysqldump for each as follows:

mysqldump -udb_account -pdb_pw -hserver.com --single-transaction --flush-logs
   --routines --triggers --quick $fn 2> $fn.err | gzip > $fn.mysql.gz

Issue: many of these databases have hundreds of tables (ever increasing). For these larger dbs the mysqldump command often exits after only a single table. If I run the command from a terminal session it executes correctly. (typically it runs as a cron job 1x/wk)

The .err file contains no messages. Neither does the server.err file in the MySQL root dir.

Note: this script had been running fine on MySQL 5.5 for several years. This problem started happening when I upgraded to 5.6.

Also: the --flush-logs portion isn't working. The mysql_bin folder has never been emptied since this system was brought online.

One variable that I haven't controlled for yet: when run as a CRON job the script forks 3 processes at a time. When I test the command in a term session I'm only doing one at a time.

System in question:

CentOS 6.4
x64
64Gb RAM


texts: Is my shopping list database schema good enough?

texts:
I'm wondering if my schema is correct? It's intended to hold shopping list items for each user, a user belongs to a household. Items will be added to the household.

The stocklist will to hold the quantity of an iID (item ID) and that iID is belongs to a hhID .

So at the moment

stocklist.hhID references household.hhID
household.adminID references users.uID
items.hhID references household.hhID
stocklist.iID references items.iID

I'm wondering if these relationships are viable? Even more importantly what do you think of the schema?

Posted from my unused account before.


texts:
My query is not using indexes.
it was taking around 5 seconds.
output is 500 rows only. it is a simple query

Please find the explain plan and table structure here.

Explain Plan

mysql> explain SELECT COUNT(*) AS `records_found`
               FROM `builds` AS `build`
               WHERE (`user_id` = '32762'
               AND `country_id` = 'gb'
               AND `share` = 0
               AND `mode` = 'bouquet'
               AND `build_data` != '');

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | build | ALL  | NULL          | NULL | NULL    | NULL | 35209 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Table structure

mysql> show create table builds\G
*************************** 1. row ***************************
       Table: builds
Create Table: CREATE TABLE `builds` (
  `build_id` varchar(8) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `derivation` int(11) NOT NULL,
  `share` tinyint(4) NOT NULL,
  `country_id` varchar(2) NOT NULL,
  `build_data` text NOT NULL,
  `mode` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`build_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


texts: Data Collection reports fail because login is from an untrusted domain

texts:
I've set up a data warehouse and Data Collection according to BO. Reports are generated properly when running them in Management Studio running on the server but fail when they are run from Management Studio on a different machine (which is using VPN to connect to the network the SQL Server is on). The error message is:

  Failed to connect to server x.x.x.x. --> Login failed. The login is
  from an untrusted domain and cannot be used with Windows
  authentication.

The server uses mixed mode authentication. Data Collection is configured to use an SQL Login and the login that tries to generate the report is an SQL Login as well. They've got nothing to do with domains. Why do I get this error?


texts:
I have a table for sensor data, simplified it looks like this:

DEVICE_ID NUMBER
SENSOR_ID NUMBER
DATA_TIME NUMBER
DATA_VALUE1 NUMBER

The table constains billions of rows and is partitioned by week number, DATA_TIME is in Unix time (milliseconds since 1970). I have two indexes on this table, IX1 on (DATA_TIME DESC) and IX2 on (DEVICE_ID,SENSOR_ID,DATA_TIME). IX2 was created yesterday.

My query wants to see the data for one sensor in the current month and goes like this:

SELECT /*+ PARALLEL (12) */
   data_time,
   data_value1
FROM table
WHERE device_id = 1041
  AND sensor_id = 202
  AND data_time BETWEEN 1383304859 *1000 AND 1385464859 *1000
ORDER BY data_time ASC

I was surprised to find that the optimizer ignores IX2 and comes up with the following plan:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    65 |  1365 |     5  (20)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                         |                              |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)                    | :TQ10001                     |    65 |  1365 |     5  (20)| 00:00:01 |       |       |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                        |                              |    65 |  1365 |     5  (20)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE                          |                              |    65 |  1365 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE                      | :TQ10000                     |    65 |  1365 |     4   (0)| 00:00:01 |       |       |  Q1,00 | P->P | RANGE      |
|   6 |       PX PARTITION RANGE ITERATOR       |                              |    65 |  1365 |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS BY LOCAL INDEX ROWID| table                        |    65 |  1365 |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWP |            |
|*  8 |         INDEX RANGE SCAN                | ix1                          |     1 |       |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter("DEVICE_ID"=1041 AND "SENSOR_ID"=202)
   8 - access(SYS_OP_DESCEND("DATA_TIME")>=HEXTORAW('38FDD8C8BEA9A4FF')  AND SYS_OP_DESCEND("DATA_TIME")<=HEXTORAW('38FDD8DDFAA9A4FF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))>=1383304859000 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))<=1385464859000)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

The statistics on the table and the indexes are recent. I found some SQL Baselines that were created for this statement and removed them.
And on top of that, the SQL tuning advisor recommended creating an index on (DEVICE_ID,SENSOR_ID,DATA_TIME,DATA_VALUE1).


texts: MySQL replication 'connecting to master'

texts: Anyone please give detailed documentation on MySQL Replication on Windows system. I replicated master to slave, but it is showing 'connecting to master'. Error number showing as 1045 and 2003 it's varying.


texts: Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number

texts:
I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must double quote it to work with it, for example, SELECT * FROM crs_1973."2015_04_21"; (this works for me). However, I want to extract information about this table from the PostgreSQL information_schema. Specifically, I need the data types of the columns in the table 2015_04_21,

I have tried this query with a table whose name starts with a letter and it produces the desired result:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'some_table_name_that_starts_with_a_letter'
ORDER  BY ordinal_position;

However, when I run this query with the tables whose names begin with a number, it does not. For example:

WHERE  table_name = 'crs_1973.2015_04_21'

does not work. Nor does:

WHERE  table_name = 'crs_1973'.'2015_04_21'
WHERE  table_name = 'crs_1973'."2015_04_21"
WHERE  table_name = 'crs_1973'.'"2015_04_21"'
...

etc. How do I make this query work for a table name that starts with a number? Is this possible?

There are tables called "2015_04_21" in several different schemas so I need to pass a qualified table name to the query.


texts:
Where is the problem with my code. It should work, but Compiler says:

Error(5,14): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY'
Error(6,1): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY'
Error(6,25): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY'
Error(7,78): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY'
Error(10,1): PLS-00103: Encountered the symbol "CREATE"

in this:

DROP TABLE Grupy CASCADE CONSTRAINTS ;

CREATE TABLE Grupy
  (
    idGrupy NUMBER NOT NULL ,
    nazwa   VARCHAR2 (20) NOT NULL ,
    opis    VARCHAR2 (150)
  ) ;

ALTER TABLE Grupy ADD CONSTRAINT Grupy_PK PRIMARY KEY
(
  idGrupy
)
;

CREATE OR REPLACE TRIGGER wyzwalacz
BEFORE INSERT OR UPDATE OF nazwa ON Grupy
FOR EACH ROW
DECLARE
stara_nazwa VARCHAR2;
BEGIN
dbms_output.put_line('Wstawiono nową nazwę do tabeli Grupy');
stara_nazwa:=:new.nazwa_grupy;
:new.nazwa_grupy:=upper(:new.nazwa_grupy);
dbms_output.put_line('Nazwa grupy '||stara_nazwa||' została zmieniona na '||:new.nazwa_grupy);
END;

Insert into Grupy Values(1, 'Vw AG','Należą do niej marki :Audi, Bentley, Bugatti, Ducati, Lamborghini, Seat, Skoda, Volkswagen, Scania, MAN, Porsche');

select * from Grupy;


texts: How can I re-index a column using SQL Server Compact Edition?

texts:
We have a table TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical.

The query requirement is that the column ListIndex has to start from 0 and be sequential for each parent / child(ren) relationship.

The query I came up with is as follows:

SELECT  Id,
        ParentId,
        Sub.NewIndex AS ListIndex

FROM    TemplateItem
JOIN    (
            SELECT  Id,
                    ROW_NUMBER() OVER
                    (
                        PARTITION BY ParentId ORDER BY ListIndex
                    )
                    - 1 AS NewIndex

            FROM    TemplateItem
        )
        AS Sub ON TemplateItem.Id = Sub.Id

( unnecessary bits of query removed for readability )

This works perfectly fine in SQL Server 2008 R2.

However, I now need to use this same functionality in SQL Server Compact Edition 4.0 but, as you may already know, it will fail.

It does so because there is no ROW_NUMBER() in SQL Server Compact Edition 4.0.

How can I accomplish this same functionality?


texts: Get all rows where specific column value occurs more than once (filter out single occurences)

texts:
I am trying to filter all sessionIds that occur once out of an existing result set.
This query is being used in a web application and runs on a big dataset (~ 35 million rows), so I want to prevent having subqueries here.
I tried this, which provides a filtered result, except that I now only get one row for each sessionId (and I want every request and response):
CREATE TABLE `api_log` (
  `id` varchar(50) NOT NULL,
  `clientId` varchar(100) DEFAULT NULL,
  `inserted` int(11) DEFAULT NULL,
  `sessionId` mediumtext DEFAULT NULL,
  `stage` varchar(120) DEFAULT NULL,
  `request` longtext CHARACTER SET utf8mb4 DEFAULT NULL,
  `response` longtext CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO api_log
  VALUES
    ("1", "abc", 1621008484, "session1", "production", '{"key":"value"}', '{"key":"value"}'),
    ("2", "abc", 1621008494, "session2", "production", '{"key":"value"}', '{"key":"value"}'),
    ("3", "abc", 1621008584, "session1", "production", '{"key":"value"}', '{"key":"value"}'),
    ("4", "abc", 1621008684, "session2", "production", '{"key":"value"}', '{"key":"value"}'),
    ("5", "abc", 1621008784, "session3", "production", '{"key":"value"}', '{"key":"value"}'),
    ("6", "abc", 1621008884, "session4", "production", '{"key":"value"}', '{"key":"value"}'),
    ("7", "abc", 1621008984, "session5", "production", '{"key":"value"}', '{"key":"value"}'),
    ("8", "abc", 1621009084, "session6", "production", '{"key":"value"}', '{"key":"value"}'),
    ("9", "abc", 1621009184, "session7", "production", '{"key":"value"}', '{"key":"value"}'),
    ("10", "abc", 1621009284, "session8", "production", '{"key":"value"}', '{"key":"value"}');

SELECT
    `clientId`,
    `sessionId`,
    `inserted`,
    `stage`,
    `request`,
    `response`
FROM
    `api_log`
WHERE
    (stage = 'production')
    AND (clientId = 'abc')
    AND (
        `inserted` BETWEEN 1621008482 AND 1621009285
    )
GROUP BY
    `clientId`,
    `stage`,
    `sessionId`
HAVING
    COUNT(sessionId) > 1

Is there any trick to get all rows where a sessionId occurs more than once?
In this case, I get two rows, one for session1 and one for session2, but I am missing two more because both mentioned sessionIds have an additional row that should match.
SQL fiddle: http://sqlfiddle.com/#!9/f87bb54/3


texts: Work out date difference when there is only one date

texts:
I have 2 records, one details when the stop clock was stopped and the other started.  I only have the one date value in each record.  I'm trying to work out the difference between the stop and start dates.  I'm looking for pointers.  I thought by doing something like:

SELECT e1.ID, MIN(e1.EventTime) AS [StopTotalMinutes]
          ,MAX(e2.EventTime) AS [StartTotalMinutes]
  FROM event AS e1
  JOIN event AS e2
    ON e1.ID = e2.ID

I would have been able to work something out, but I'm failing.

So, Table and some sample data is:

ID    |    EventTime     |   Action
1       2012-01-01 10:00      Stop
1       2012-01-01 13:31     Start
1       2012-01-03 09:45      Stop
2       2012-01-01 11:12      Stop
2       2012-01-10 15:35     Start
2       2012-01-11 08:39      Stop


texts: Invoke-PolicyEvaluation fails with xml files on network location

texts:
I am just starting to explore using Policy Based Management in SQL 2016. I created a policy that runs in SSMS just fine. I exported the policy to a folder on the local machine (VMSk1) that is shared to Everyone/read. I go to a second machine (VMSk2) and execute the following powershell. The idea was to have a central location for the xml export files and run on any server....

$Path = "\\\vmsk1\PolicyExports\AllDatabasesInSync.xml"
Invoke-PolicyEvaluation -Policy $Path -TargetServerName $env:computername

...I get the following error...

  Invoke-PolicyEvaluation : File 'Microsoft.PowerShell.Core\FileSystem::\vmsk1\PolicyExports\AllDatabasesInSync.xml' does not have a valid XML format that can be deserialized to a policy.

If I copy the file to VMSk2 and run...

$Path = "c:\PolicyExports\AllDatabasesInSync.xml"
Invoke-PolicyEvaluation -Policy $Path -TargetServerName $env:computername

...the policy will execute.

I tried

$Path = "\\\vmsk2\PolicyExports\AllDatabasesInSync.xml"

and it failed.

So I am left with the conclusion that the cmdlet will not work with an xml file located anywhere but a local drive; which seems just plain wrong.

Am I missing something? Why can I not execute the policy using an xml export file from a network share?


texts: Mysql does not show correct count on rows

texts:
I have this mysql installation that i am using to test some java program that has multiple threads.At first, i noted that some rows dissapeared and i thought it was because of this error InnoDB: page_cleaner: 1000ms intended loop took 5745ms. The settings might not be optimal.

I fixed that SET GLOBAL innodb_lru_scan_depth=256; by running that code and i also went to the optios page on the mysql workbench and edited and saved and also restarted my machine and the mysql server.

My java program has around 50 threads and from watching activity on the java jconsole program, i can tell that multiple threads are inserting data into the mysql table.

I am on windows and using heidisql and mysql workbench. When i check the number of rows on the command prompt, i get 32 rows

and a million plus rows when i look at the heidisql interface

I tend to believe that there are more than 1 million rows since i am using that many threads.

is there a reason why the table is showing 32 rows instead of a million plus rows?

Update


texts: Fastest MySQL pagination and ORDER from a huge UNION ALL VIEW

texts:
Let's say we have a schema with table 'month' which have the current information and table 'year' which have the historic information. Each table has around 65,000 rows.

This schema exists in several places, let's say place A, B, and C.

It's requested to fetch ordered information from every schema. No possible 'materialized view'

According to me, I need to unite all the results (without LIMIT):

CREATE VIEW all_results AS
SELECT * FROM A.month
UNION ALL
SELECT * FROM A.year
UNION ALL
SELECT * FROM B.month
....

...and then apply the ORDER BY, the COUNT and the LIMIT

SELECT * FROM all_results ORDER BY column1 LIMIT 0,23
SELECT count(*) FROM all_results

but this approach is taking almost 20 seconds (which is too much for a web solution).

Is there a better way to do this?

(Added from dup question)  This variant also takes 20 seconds.

SELECT SQL_CALC_FOUND_ROWS * FROM(
SELECT * FROM A.month
UNION ALL
SELECT * FROM A.year
UNION ALL
SELECT * FROM B.month
....
) AS united
LIMIT 0,23

And the count the total: SELECT FOUND_ROWS()


texts: Stored Procedure error “Must declare the scalar variable”

texts:
I am trying to create a stored procedure with dynamic SQL inside of it.
The proc receives 5 variables, 3 of which are names of columns and fields and another 2 are for OFFSET and FETCH NEXT part.
I keep getting this error on the 2 last variables:

Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@numOfOffset".
Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@numOfRows".

I will appreciate some help, please

IF (SELECT 1 FROM sys.procedures WHERE [name] = 'sp_targil1') = 1
    DROP PROCEDURE sp_targil1;
GO

CREATE PROCEDURE sp_targil1 (@tableName varchar(30), @fieldNameAggBy varchar(30), @fieldNameToAggOn varchar(30)
    ,  @numOfOffset int, @numOfRows int
    )
AS
BEGIN
    DECLARE @query1 varchar(500)
    DECLARE @qaeryMain varchar(500)
    DECLARE @maxNumberOfRows int

    SET @qaeryMain =
    '
    SELECT ' + @fieldNameAggBy + ', SUM(' + @fieldNameToAggOn + ') AS ' + @fieldNameToAggOn + '_Sum_By_' + @fieldNameAggBy +
    ' FROM ' + @tableName +
    ' GROUP BY ' + @fieldNameAggBy +
    ' ORDER BY SUM(' + @fieldNameToAggOn + ') DESC OFFSET @numOfOffset - 1 ROWS FETCH NEXT @numOfRows ROWS ONLY'
    PRINT @qaeryMain
    EXECUTE (@qaeryMain)
END


texts: One "Faster" server slower than the other

texts:
I have 2 VMs:

VM1 - 8cores 32gb RAM
VM2 - 8cores 64gb RAM

I'm running identical process on both VMs at same time using SSIS Catalog, all processes are executed on it's own VM so no information travels through the network.. Aaaand I found out that load times for these processes are quite slower on VM2.

Load:

VM1- 15m
VM2- 45m

I have checked some performance metrics and did some basic tests... During the load I can see Network I/O Waits

VM1- 600ms/sec
VM2- 750ms/sec

I assume this network I/O wait comes from SSIS as SSIS is executing a query against SQL Server and then inserting the information in batchs, waits statistics tells me that both cases wait type is ASYNC_NETWORK_IO.

Also I did a test using SP_SPACEUSED to see the amount of rows inserted in both tables at same time and the results are not super accurated but VM1 almost doubles the amount of rows inserted...

Any further tests I'm missing? Any other checks that comes to your mind?


texts: Sql Server 2012 Change Data Capture weird behaviour

texts:
In my Sql Server 2012 I have the following table in a database

CREATE TABLE [dbo].[HumanResource](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IsHired] [bit] NULL,
    [Address] [nvarchar](max) NULL,
    [Score] [bigint] NULL,
    [LastUpdate] [datetime] NULL,
 CONSTRAINT [PK_HumanResource] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

when I update the Address field (nvarchar, not null), it is captured as below

   NULL       3      2   mahmood True    here    111 2014-09-01 14:42:28.913
   NULL       4      2   mahmood True    there   111 2014-09-01 14:42:28.913

but when I update the Score field (nvarchar, null), this is captured

   NULL       3      2   mahmood True    NULL  111 2014-09-01 14:42:28.913
   NULL       4      2   Mahmood True    there   111 2014-09-01 14:42:28.913

**Edit (the update and select queries) :

update HumanResource set Address = 'blah blah blah' where id = 2
update HumanResource set Score = Score + 1 where id = 2

SELECT        TOP (10000) __$start_lsn, __$end_lsn, __$seqval, __$operation,
__$update_mask, ID, Name, IsHired, Address, Score, LastUpdate
FROM            cdc.dbo_HumanResource_CT

Considering that sql server Update is a chained Delete and Insert, the first row is the delete row capture and the second one is the insert row capture.

But I don't understand why the address is tracked as NULL when I update other fields, I would appreciate any information on this issue.


texts: SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle

texts:
I am trying to Export Data from SQL Server to Oracle using the "Microsoft OLEDB Provider for Oracle".

I am using "Export Data..." option from Task menu.

I get to connect to the source database (SQL Server) but when I try to connect to the destination database (Oracle) using "Microsoft OLEDB Provider for Oracle" option, I get the following error.

"ORA-06413: Connection not open"

I also checked my connections (tnsnames.ora) and I am able to successfully connect using  SQL Developer to my local Oracle XE instance. My OS is Windows 8 running on a x64 machine.

Any guess what could be the problem here?


texts: Oracle - Archivelog files , can we delete them?

texts:
We have RHEL 7.2 machine where Oracle is installed. Recently this machine ran out of space and we figured out that most of the space is going into "/home/oracle/ORA12C/fast_recovery_area//archivelog"

Within this folder , we have multiple datewise folder and each folder contains .arc files
Is it ok to directly delete folder which are one year ago?


texts:
I'll have a table where:

userid (link to user's id in users table)
foldername (a varchar, max. 260 char)
entry (varchar, max. 1024)
data (varchar, max. 20kb)

Single user will have several foldername and entries.
The point is, queries will never ever search anything inside entry or data. Queries ONLY will be like
select * from data where userid  = 1 and foldername = 'first';

That's it.
So I thought about two options:

a. Instead of entry and data columns, storing them inside HDD and only storing filename in DB, so when query returns, PHP app. will read filename, then read the file and send it to user in HTTP response.
b. Store them in DB as I said above

But I'll never run queries on them, so no sorting/indexing will be needed and no full text search will run ever.
QUESTIONS

What's the best possible optimization here?
How can I define a column as a "not-really-a-database-entry" type in MySQL, or is file better?


texts: Could not start the SQL Server Agent (SQLEXPRESS)

texts:
Possible Duplicate:
  Sql Server agent can not start

I could not start the SQL Server Agent (SQLEXPRESS) instance as it will give me this error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

I search for some solution and they say that I should remove SQL Server 2008 Express Edition. However, I do not have SQL Server 2008 Express Edition and thus did not install it.

Also, I notice another SQL Server Agent (MSSQLSERVER), in which I am able to start it and let it run. However, I try using my local account and even the sa account, I could not get into this instance. (The SQL Server (MSSQLSERVER) is running too)

So, may I know what am I missing?


texts: Access denied error in trying to connect to a MySQL db in Python

texts:
I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct).

Here is what I’ve written so far:

import socket
from ssh2.session import Session
import mysql.connector
host = 'servername.logserverlog.net'
user = 'username'

sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((host, 22))

session = Session()
session.handshake(sock)
session.userauth_publickey_fromfile(user, r'C:\Users\user\Docs\ssh-key')

cnx = mysql.connector.connect(user='username', password='$gHj1aFaVFRfhl*C', database='analyst_db')

The error I am getting reads:

  File “C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site- packages\mysql\connector\connection.py”, line 176, in _auth_switch_request raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user ‘username’@‘localhost’ (using password: YES)

I have already confirmed my user and password are valid.
I tried passing the password string as a raw string, to see if somehow the Python string syntax wasn’t being received by the MySQL db correctly, and still received the same error.
User privileges for the user on the database have been confirmed as GRANT ALL.
adding host='localhost' or host='127.0.0.1' to the parameters for the "cnx" object produces the exact same error.

So, I’m not sure where to go from here.


texts: Does HP Vertica run on Windows and is it compatible with our existing Oracle SQL?

texts:
I am new to HP Vertica. I read the installation documentation for HP Vertica, but it seems to be totally unix focused.  Can we install HP Vertica under Windows?

Additionally, will we need to update our existing Oracle SQL commands for use with HP Vertica?

I'm also unclear on the Vertica terminology:

Host: is a computer where the Vertica is installed.
Instance: it is also a computer where the Vertica is in running state.
Node: it is the combination of HOST & INSTANCE.
Cluster: I am not clear about this. May be this is about the instances of Vertica running on different computers.
Database : It is the actual installed database software where the data & catalog resides to which other nodes are connected (not sure about this also).

Please help me understand the basic concepts.


texts: MySQL - Binary Log File size growing more than the maximum limit

texts:
I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter max_binlog_size = 1073741824 which is equivalent to 1GB. This means, once a binary log reaches the size of 1GB, a new file should be created.

I observed in my server that all the binary log files are of different sizes with lot of contrast like 200GB, 165GB, 4GB etc...

Why does this happen when the maximum size is 1GB for a binary log file? Is it necessary to mention even the default maximum size explicitly in the configuration file?


texts:
After recently migrating MySQL 5.1 to a new, faster server, I'm seeing occasional and seemingly random periods where some queries are running slowly. Despite a lot of investigation, I can't find the source of the problem.

I've listed a few concrete questions at the end of this writeup, but otherwise I'm not sure where to go at this point. If you can suggest any possible solutions or tools I can use, I would be very grateful.

Symptoms and Background

Every day or two, a small group of queries will appear at random in my slow query log, normally within a period of two or three minutes. These queries, which typically execute within 30-60 milliseconds, are taking vastly longer -- in the range of 6 to 20 seconds. The slow query log doesn't indicate any lock time for these queries.

The problems became obvious after migrating to a new database server. Both servers run MySQL 5.1 under CentOS 6, and both use SSDs for database storage. The new server, unlike the old one, runs two SSDs using Linux software RAID-1. Although the hardware has changed (different datacenter, different network), the software hasn't. The CentOS and application versions were the same at the time of the migration. The application code has also remained the same.

My Analysis/Troubleshooting

I have no reason to believe the queries themselves are problematic or that the database is experiencing performance problems; these aren't complex or unoptimized queries. They normally execute in <100ms, and the database server has a typical load average less than 1.0.
I don't believe that all queries are affected during these periods of slowness. The server sees, on average, about 8000 queries per hour. But my slow query log is showing only a handful (maybe 5-10) during the slow periods.
The queries appear to have nothing in common. They are mostly select statements against several different databases involving tables that use both InnoDB and MyISAM engines. Sometimes update queries will also appear.
I tried reducing the slow query time to 2 seconds (from 5 seconds) to find out if there were more periods of slowness leading up to the very long-running queries. I don't see that happening. The slow periods seem to start and stop suddenly and inexplicably.
Suspecting that IO was a problem, I setup the sar utility to capture system stats every minute. I can find no correlation between the periods of slowness and the system or IO load reported by sar. The server appears to be nearly idle when the slow queries are logged.
I examined all cronjobs as well as all logfiles in /var/log, but can't find anything interesting happening at the same time as the slowness.
Just to be safe, I disabled r1soft (backup software) and disabled other backup-related tasks. I also reduced the IO priority of Sphinx, a search engine that runs on the same server. Neither had any effect.
I tried to disable the query cache, as I've read that it can cause strange lock situations. No effect.
I upgraded MySQL to version 5.5, just to see what would happen. This also had no effect on the slow query issue.

Questions

Is there any way I can find out if some strange, intermittent hardware issue with the new server is in play here? As I wrote above, I haven't noticed anything while carefully checking the system logs.
Is there any chance that network latency or packet loss has any role in this? I assume not, as the query time recorded in the slow query log should exclude any network delay, right?
I see that MySQL 5.5 offers a Performance Schema feature that is new to me. But it's not obvious to me how this can be used. Is there some way I can use this to troubleshoot the problem?

Thanks very much for any help with this frustrating problem!


texts: How to find/calculate time taken for synchronization in SQL Always on AG?

texts:
I have an SQL Always On Availability Group (AG) with 4 Servers (Replica).

I'm going to add a new database (about 200GB) in the AG.

I want to measure the time taken for the dB synchronization (Complete & for the First Time), in remaining 3 servers.

How do I get this done ?

Environment: SQL Always On 2016. AG.


texts: Pivot query issue while converting row to column

texts:
I have these two tables:
Table 1: tbl_bulkuploadfields

fid |  categoryid | fieldname
--------------------------------------
1   |      1      | educationlevel
2   |      1      | institution_name
3   |      1      | course
4   |      1      | year_of_passing
5   |      1      | percentage

Table 2: tbl_bulkuploads

category | fieldname | fieldvalue
-----------------------------------
1        |     1     | SSC
1        |     2     | H.B.Kapadiya
1        |     3     | Primary school
1        |     4     | Mar-2000
1        |     5     | 89%
1        |     1     | HSC
1        |     2     | Vishwaniketan
1        |     3     | Higher Secondary
1        |     4     | Mar-2002
1        |     5     | 70%

Below is the structure which I want as a result of a pivot query:

category | educationlevel | institution_name | course | year_of_passing | percentage
------------------------------------------------------
   1     | SSC            | H.B.Kapadiya     | Primary School | Mar-2000 | 89%
   1     | HSC            | Vishwaniketan    | Higher Secondary|Mar-2002 | 70%

I have written the below query but it is returning only a single row instead of multiple rows:
select distinct B.Category,
MAX(IF(tbl_bulkuploadfields.fieldname = 'educationlevel', B.fieldvalue, NULL)) AS educationlevel,
MAX(IF(tbl_bulkuploadfields.fieldname = 'institution_name', B.fieldvalue, NULL)) AS institution_name,
MAX(IF(tbl_bulkuploadfields.fieldname = 'course', B.fieldvalue, NULL)) AS course,
MAX(IF(tbl_bulkuploadfields.fieldname = 'year_of_passing', B.fieldvalue, NULL)) AS year_of_passing,
MAX(IF(tbl_bulkuploadfields.fieldname = 'percentage', B.fieldvalue, NULL)) AS percentage  from tbl_bulkuploads B
inner join tbl_bulkuploadfields on tbl_bulkuploadfields.fid = B.fieldname
where B.Category = 1 GROUP BY B.Category


texts:
The following seems likely to be a fairly common situation, but I haven't seen any solutions.

I have a PostgreSQL database containing views that are specific to the $user, in order to restrict data depending upon some database-specific ACL logic. The database is typically accessed through JDBC, and in particular in JSP for a web interface. Users log in to the website with password authentication using LDAP.

What I would like is to make use of the views from within the JDBC connection to the database. But typically this mid-tier connection is created using a generic user, rather that specific to the person logged in to the website. I believe that in Oracle the "correct" approach is Proxy Authentication, but AFAIAA there is no equivalent for PostgreSQL. So what to do instead?

Some ideas come to mind:

Give all users' database accounts the same password, and then create user-specific JDBC connection using this generic password (this just feels so wrong...)
Store the user's login password somewhere in the website, to re-use when creating a database connection (again, this feels very wrong)

Any other (hopefully better) suggestions?


texts: Forcing execution plan with local join hints

texts:
I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.

In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:

List item

SORT(Distinct Sort)
Stram Aggregate(Aggregate)

Are these actions something I can choose, or are they dependent on join types/order choosed during the query?

Both plans are created by XML extracted from Query Store.

Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V

Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4


texts:
I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)
INSERT INTO [dbo].[Table_1]([Test]) Select 1

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help


texts:
On the Postgres ODBC driver page the name for each file is of the form:

psqlodbc_09_01_0100-x64.zip
psqlodbc_09_01_0100.zip
psqlodbc_09_01_0100-1.zip
psqlodbc_09_01_0200-x64.zip
psqlodbc_09_01_0200.zip
psqlodbc_09_01_0200-1.zip
psqlodbc_09_02_0100-x64.zip
psqlodbc_09_02_0100.zip

I'm trying to make sense of the file names, and haven't been able to find any documentation. The 09_01 is clearly the major/minor version and the x64 is pretty clear. What do the middle terms, like 0100-1 mean?


texts: Why do cross-database statements break replication when using filters?

texts:
For example, say you you have table A and table B on the master and on the slave you've set up replicate-do-db = A.

Now if I do something like this:

USE B;
DROP TABLE A.sometable;

Why should this not work?  I know why it doesn't work in the sense that I've used a database then operated on another one but why has MySQL been implemented this way?
Was it a conscious decision or is there some grander reason why this shouldn't/can't be made to work?


texts: Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

texts:
PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a bit different than the original R-Tree, an observation being that the split technique is the Korotkov split. Furthermore, on PostGIS source, there are comments suggesting R*-tree ideas. I am somewhat unsure...

So my question is: is the "R-Tree behaviour" offered in PostgreSQL the vanilla R-Tree? If not, what are some of the differences? Is this data loaded insert by insert or is there a bulk loading mechanism in place?


texts: Query to update one column of a table based on a column of a different table

texts:
I have two tables with below structure :
Create table Customer_info
(Customer_num  varchar(50),
 Customer_Branch  int);

Create table Customer_Transaction_Info
(Customer_num     varchar(50),
 Branch_Code      int,
 Trns_Measure_One int,
 Trns_Measure_Two int
 );

Sample data for each table:
insert into Customer_info(Customer_num , Customer_Branch)
values('A',1),
      ('B',2),
      ('C',3);

   Customer_num     Customer_Branch
------------------- ---------------
     A                      1
     B                      2
     C                      3

insert into Customer_Transaction_Info (Customer_num,Branch_Code,Trns_Measure_One,Trns_Measure_Two)
Values ('A',1,10,0),
       ('A',2,20,5),
       ('A',3,14,0),
       ('B',2,10,10),
       ('B',1,36,0),
       ('C',3,14,0),
       ('C',1,10,18);

 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0
    A               2           20               5
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0
    C               1           10               18

What I need to do is this:

For each Customer_num in Customer_Transaction_Info , Trns_Measure_Two must be assigned to the correct Branch_Code for that customer which is the one in the first table , Customer_info

For each Customer_num in Customer_Transaction_Info, Trns_Measure_Two must be updated to 0 for  incorrect branches.

So based on these the desired result is below:
 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0---> Update to 5
    A               2           20               5---> Update to 0
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10---> This record is Correct
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0 ---> Update to 18
    C               1           10               18---> Update to 0

We must take these facts into consideration that:

There's only one Customer_Branch for each Customer_num in Customer_info table.
There's only one non-zero Trns_Measure_Two for each Customer_num in Customer_Transaction_Info

Thanks in advance


texts: Updating tables between different SQL Server databases

texts:
I have two SQL Server databases (A and B), and they are running on separate machines. In order to make A same as B, I used to backup B, copy the backup to A machine and restore it as A database.

A problem was that restoring updates all tables in A database. What I want to was update specific tables in A database with those in B database. What can I do? Here are more information:

A database is live production so that I cannot set it single user mode.
The specific tables in A database are being read by web servers. They are neither being updated nor changed.

Simply speaking, I want to update specific tables in live database without letting web users blocking.


texts:
Assuming that we have two tables:

Table Q
D E
a 1
b 2
g 1

Table R
D E
f 1
b 2
h 3

Show the result of a set difference between tables Q and R. My answer is

D E
a 1
g 1

But my classmates said the instructor's answer is

D E
a 1
g 1
f 1
h 3

So it looks like symmetric difference rather than the real "difference", which answer is right? My classmates said that the question use the word "Between" so the answer should include two tables, but I do not think so.


texts:
I'm developing a database for time series analysis of price bars for stocks.  The associated metrics ("technical indicators") will derive on as many as 10 successive layers of dependent set operations. Using Postgres 10.3.

There are two ways to derive a layer-1 metric "metric1" that becomes an input to most of the other metrics. The only difference between the datasets after the 1st layer of derivations is the metric1 value. The two datasets use the same set of table specs.

I want to to see how the two different derivations of metric1 impact the higher-level metrics, with the implementation goal of doing so with minimal coding and operational overhead.

My impression is that putting the datasets into separate Postgres schemas could meet the implementation goal.  However, all the example Postgres schema use cases I've googled relate to segregating data that remains separate, for privacy, or to manage different groups or data releases. I have not found examples where the intent is to compare derivations from one schema to those from another schema.

So two related questions: is there anything about a two-schema approach that would be problematic for this use case, and are there are other approaches that would be preferable ?


texts: sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text

texts:
This is something that's bugged me though has never caused any real issues as I can generally locate this information by other means, but can someone explain why NULL may be returned in the dbid, objectid, and number columns from the sys.dm_exec_sql_text DMV, yet yield results in the text column?  I understand why output would return inversely (e.g. all columns but text would yield data), but I've seen this behavior a number of times across a number of SQL Server versions and the documentation doesn't seem to imply this should be possible, or I'm just reading it wrong.

Here's an example of the query I'm running:

SELECT TOP 100
      t.*
    , s.*
    , c.*
FROM    sys.dm_exec_query_stats s
        LEFT JOIN sys.dm_exec_connections c
               ON c.most_recent_sql_handle = s.sql_handle
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE s.execution_count > 1 AND DATEDIFF (second, creation_time, GETDATE()) > 0
        AND t.dbid IS NULL

Here's a sample of one of the results from the text column which implies this isn't related to temporary objects which is what I would normally guess to be the cause.

select * from [dbo].[Map_ProviderSpecialty]

What situation is occurring where these columns are returning NULL values?


texts: Pre Caching Index on a large table in PostgrSQL

texts:
I have a table with about 10mln rows in it with a primary key and an index defined on it:

create table test.test_table(
    date_info date not null,
    string_data varchar(64) not null,
    data bigint
    primary key(date_info, string_data));
    create index test_table_idx
    on test.test_table(string_data);

I have a query that makes the use of the test_table_idx:

select distinct date_info from test.test_table where string_data = 'some_val';

The issue is that first time around it could take up to 20 seconds to run the query and < 2 seconds on any subsequent runs.

Is there a way to pull load the entire index into memory rather then have DB load information on first access?


texts: Is introducing a where condition into the join clause bad practice

texts:
In the query below, the condition

and r.master_seq=206

is used on the on clause as a join condition.
In the programme code, the value for r.master_seq is set by a parameter.

My concern is that I'm not sure if it's ok to mix in this condition(r=.master_seq=123, that I believe should be a where condition) into the join condition.
I was thinking that I should only use relational information(primary keys and foreign keys) in the join clause, not a where condition ( where id=123, balance>300, etc).

SELECT
  *
FROM
TB_REQUEST_DATA D

left outer join
TB_SPOT_REPORT_REQUEST r
on D.SEQ = r.REQUEST_SEQ
and r.master_seq=206

WHERE
  d.SEQ IN (
  7,12,5,11,4
  )

desc tb_request_data;
Name          Null     Type
------------- -------- -------------
SEQ           NOT NULL NUMBER
REQUEST_NM    NOT NULL VARCHAR2(50)
REG_DT        NOT NULL DATE
REG_ID        NOT NULL VARCHAR2(20)
UP_DT         NOT NULL DATE
UP_ID         NOT NULL VARCHAR2(20)
DISASTER_TYPE          VARCHAR2(500)

desc TB_SPOT_REPORT_REQUEST;
Name         Null     Type
------------ -------- -------------
MASTER_SEQ   NOT NULL NUMBER
SEQ          NOT NULL NUMBER
REQUEST_SEQ  NOT NULL NUMBER
REAL_FILE_NM          VARCHAR2(100)
DOWN_FILE_NM          VARCHAR2(100)


texts: Order In WHERE Clause Matter For SARGability?

texts:
Suppose that I have a table with 1000 records and I have the below query, which would return 7 total records:

SELECT *
FROM MyTable
WHERE IndexedColumn > 5000
     AND OtherIndexedColumn = 2

Since the two columns have up to date indexes, SQL Server can make assumptions and find the values faster and both queries are SARGable (ideally fewer reads).  However, suppose that I need to make sure that the value is not equal to another value, let's say 12 for a different column, so I would have to add

AND AnotherIndexedColumn <> 12

If that's the last statement in the WHERE clause, does SQL Server use the SARGability in the first two WHERE statements to first filter, get the 7 rows, then look to see if each row of the 7 are not equal to 12, or does it apply the <> to every row in the original data set of 1000 rows?

The reason that I'm asking is because I'm aware that I could use a subquery or CTE to do the first part of the SARGable filter, then of the 7 rows, look and see if each is not equal, but is the query optimizer already doing that behind the scenes, or is it best to do it myself?


texts:
We have a wordpress environment however this is not a wordpress question. Our scenario is that front Ends connect to a MYSQL backend (MYSQL VM1) which is replicated to a backup vm (MYSQL VM2). The replication is Master - Master.

What we're looking for is a MYSQL HA cluster with automatic failover where if VM1 dies / is unresponsive, connection fails over to VM2. Anything you'd recommend ?


texts:
I wonder the work flow of sharding cluster in Tag-Aware Sharding.

--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 3,
    "minCompatibleVersion" : 3,
    "currentVersion" : 4,
    "clusterId" : ObjectId("546df06d63a15917a8356f4e")
}
  shards:
    {  "_id" : "shard0000",  "host" : "hadoop4:27017",  "tags" : [  "TR" ] }
    {  "_id" : "shard0001",  "host" : "hadoop5:27018",  "tags" : [  "US" ] }
    {  "_id" : "shard0002",  "host" : "hadoop6:27017",  "tags" : [  "OTHER" ] }

  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "inventory",  "partitioned" : true,  "primary" : "shard0000" }

In tag-aware sharding , with mongorestore command via mongos , first all data writes to PrimaryDatabase and then migrate the appropriate chunks ?

Or it can fill the appropriate chunk according to tagRange at the beggining of the insert?

For example , when i insert {country:"US",keyword:"abc"} document , at first it goes to shard0001 or goes to the primary database shard0000  and then chunk migration to shard0001?


texts:
What's the difference of setting up CPU affinity between by Server's Task Manager and by SSMS?
Which one is more reliable?

Thanks in advance.


texts:
Consider the following code:

DECLARE @db sysname;
DECLARE @filename varchar(260);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @filename = 'C:\temp\create_database_' + @db + '.txt';
    :setvar c @filename
    :out $(c)
    PRINT $(c);
    FETCH NEXT FROM cur INTO @db
END
CLOSE cur;
DEALLOCATE cur;

I'm expecting to get multiple files created in C:\temp, one for each database, but nothing is created, and no errors are reported by SQL Server.

I've tried running this in SSMS using SQLCMD mode, and I've also tried running it from sqlcmd.exe


texts: Use PGXN to install pg_repack on postgres 9.6 on top of Ubuntu 14.04

texts:
So I have Ubuntu 14.04 with postgres 9.6 and I wanted to install pg_repack so I did

sudo apt-get install pgxnclient`
sudo pgxn install pg_repack

but I got an error (sorry I forgot the error) and I found a site saying I need

sudo apt-get install postgresql-server-dev-all
sudo apt-get install postgresql-common

I suspect that one or both of those came in as Postgresql 10 because when I did

sudo pgxn install pg_repack

it seems to work except for that it is putting the files in /usr/lib/postgresql/10/bin when I'm still running 9.6 server.

Can I just move that to /usr/lib/postgresql/9.6/bin or is there something specific about it to postgres10 that won't work if I just move it?

Secondly, how do I configure pgxn to use 9.6 and not 10?

The website here is dead so I imagine there's documentation out there that I can't get.