texts: AWS RDS PostgreSQL dump/restore - syntax error

texts:
I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following...

1) Dump EC2 DB:

pg_dump --host localhost --port 5432 -Fc --encoding='UTF8' -U postgres ProcessorDB > /home/jboss/proc_dump_jan15.dump

2) Restore from EC2:

psql -f /home/jboss/proc_dump_jan15.dump --host=blahrds.11938475.eu-west-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=ProcessorDB

I am able to connect to the remote DB but I am getting the following errors:

psql:/home/jboss/proc_dump_jan15.dump:1: ERROR:  syntax error at or near "PGDMP"
LINE 1: PGDMP
             REVOKE ALL ON SCHEMA public FROM postgres;
        ^
GRANT
GRANT
psql:/home/jboss/proc_dump_jan15.dump:5: ERROR:  syntax error at or near ""
LINE 1:     'en'
        ^
psql:/home/jboss/proc_dump_jan15.dump:14: ERROR:  syntax error at or near ""
LINE 1:     'free',
        ^
psql:/home/jboss/proc_dump_jan15.dump:21: ERROR:  syntax error at or near ""
LINE 1:     'windows',

This goes on, then changes to:

psql:/home/jboss/proc_dump_jan15.dump:316: ERROR:  invalid byte sequence for encoding "UTF8": 0xc2 0x40
psql:/home/jboss/proc_dump_jan15.dump:316: invalid command \?0?4
psql:/home/jboss/proc_dump_jan15.dump:317: invalid command \?????M?
psql:/home/jboss/proc_dump_jan15.dump:318: invalid command \)??LF,?C?{<??y??ݚ???Ɲ???0??{?dV
                                                                                ?
psql:/home/jboss/proc_dump_jan15.dump:321: ERROR:  invalid byte sequence for encoding "UTF8": 0xbd
psql:/home/jboss/proc_dump_jan15.dump:331: invalid command \?`?v?ʺ??;6????6??N??.????c?~???&?G(??
psql:/home/jboss/proc_dump_jan15.dump:335: invalid command \@(??n']??sIZ??1VZ?º???????j?+??λ?=L?E??ܡ|c?0t
psql:/home/jboss/proc_dump_jan15.dump:338: invalid command \?A???N??/hL???Q??G??r
psql:/home/jboss/proc_dump_jan15.dump:354: invalid command \??I?$?R?o??¬???NJ?-F'??x?0z??z??hŗc?
psql:/home/jboss/proc_dump_jan15.dump:357: invalid command \x?r?Y??gMb~?墶6wб??m'aem?o????]X?s?????}??u?&?;-xY8?1)?}c??-@??h?K???IA?֒qv??Hs?g??J?DoD?Y?m
psql:/home/jboss/proc_dump_jan15.dump:361: invalid command \??W?]U?22?U-??N?
psql:/home/jboss/proc_dump_jan15.dump:362: invalid command \4??im?@??ښ?S?j???ꨫ?d܊פJ?cu??l!?ް̳u?5+^O??>=?k+]???԰A??5????~??N???0??:=??ՙM?3zm
psql:/home/jboss/proc_dump_jan15.dump:372: invalid command \HF??w?
psql:/home/jboss/proc_dump_jan15.dump:378: invalid command \g?#?s?t?I,???T?}5???

Cut short for brevity...

I spotted the 'invalid byte sequence for encoding "UTF8"' and added the --encoding attribute to the dump command but that hasn't had any effect

Anyone know what the issue might be? Am I missing an option on the dump? The AWS docs are a bit vague here

Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I assume this is ok as the dump/restore will deal with the migration?


texts: How to generate list of dates --- ie every day for the past 2 years?

texts:
We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.

I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.

Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.


texts: How to fill the gap in a multipolygon in SQL Server

texts:
Using SQL Server 2014.
I have the following self-intersecting geometry that I make valid with .MakeValid() which gives a multipolygon.
declare @geomOrig geometry
select @geomOrig = geometry::STGeomFromText('POLYGON((705768.86 6193250.0725,705646.46 6193139.6725,705848.06 6193169.2725,705636.06 6193237.2725,705784.06 6193102.0725,705768.86 6193250.0725))', 25832).MakeValid()
select @geomOrig

The question is: How do I fill in the gap in the middle to create a single star-shaped polygon?


texts: MySQL query to fetch data by lang_id (if not fetch by default lang_id = 0)

texts:
I have a following table

CREATE TABLE `lang_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  `page_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

insert into lang_pages set lang_id = 0, page_id = 1, page_title = 'About Us (English)';
insert into lang_pages set lang_id = 1, page_id = 1, page_title = 'About Us (German)';
insert into lang_pages set lang_id = 2, page_id = 1, page_title = 'About Us (French)';
insert into lang_pages set lang_id = 0, page_id = 2, page_title = 'Contact Us (English)';

I need a MySQL query which fetches the page_title by page_id & lang_id, if not present it should return the one with lang_id = 0 using single query.

I have tried it as below which works fine

select * from lang_pages where page_id = <page_id> and lang_id in (0, <lang_id>) order by lang_id desc limit 1;

For example:

select * from lang_pages where page_id = 1 and lang_id in (0, 1) order by lang_id desc limit 1;

This works just fine in case you are adding page_id in the condition.
(You are welcome if you have another approach for this)

PROBLEM
How would you find all the pages with certain lang_id, say 1? The result will be like If row exists get page from that lang_id else from lang_id = 0


texts: Time Out issue, can I set the Ping Property?

texts:
I have some reports which I access through the Report Manager interface and view using a Report Viewer. If I leave a report open in the viewer then my session never times out. I believe this is because the viewer "pings" just before the session is due to time out, thus keeping the session alive.

This isn't very desirable and I would like to somehow set the viewer so that this "ping" doesn't happen. I have been trying to find a way to do this and have been coming up short as there is no obvious reference to this property in the config files or the site settings. I have found a number of references on the web to people having timeout issues using the reportviewer control in a .NET application, but none just using SSRS.

Is it possible to change this "ping" setting somehow? If so could somebody be so kind as to guide me through it as I am struggling.


texts: How to unify two records with same field?

texts:
I have a table that get records like below:

SELECT cod, name, doc1, cpf, the result is:

cod | name  | doc1 | cpf
   1 | john  |   12 |   0
   1 | john  |    0 | 123
   2 | mary  |   25 |   0
   2 | mary  |    0 | 456
   3 | andy  |   48 |   0
   3 | andy  |   55 | 789

How to unify the registers with some cod like the result below?

cod | name | doc1 | doc2 | cpf
   1 | john |   12 |    0 | 123
   2 | mary |   25 |    0 | 456
   3 | andy |   48 |   55 | 789


texts: How to optimise very simple query with subquery that only needs to execute once

texts:
SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING COUNT(products_model) > 1)

For whatever reason, it would seem that the subquery is being run for each product. I only need it to be run once really - and would have thought that mysql would intelligently realise this. Otherwise I would have written it like

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products AS a WHERE (SELECT COUNT(products_model) FROM products AS b WHERE a.products_model = b.products_model GROUP BY products_model) > 1

But bizzarely - the second query is actually faster by a factor of precisely 3 (31 seconds as opposed to 1 minute 33 seconds), despite being a "foreach" way of thinking rather than "set theory" way of thinking. I thought the latter was supposed to be much quicker? Clearly I have written the first query incorrectly - but how?


texts: Data from two table in SQL server 2008 R2

texts:
i have 2 tables:

Codes table with following column:

                         Name, Code

History table with following column:

                         Name, BU, Plan.

Now, I want code column of Codes table in place of Name column of History table.

I.e History table should contain Code, BU, Plan.

Here how can i achieve this?


texts:
My boss sent to request me that: I want to a SQL query, that lists all domain users of a specific group. I began to search query. I found several solutions using Linked Server.

EXEC master.dbo.sp_addlinkedserver
@server = N'ADSI',
@srvproduct=N'Active Directory Service Interfaces',
@provider=N'ADSDSOObject',
@datasrc=N'adsdatasource'

then

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'DOMAIN\USER',
@rmtpassword='*********'

I replaced username and password with a domain admin user's information but I got this error

  Cannot create an instance of OLE DB provider "ADsDSOObject" for linked server "ADSI".

Our domain name is akilus.local, but I didn't replace the data source.

What could the problem be?


texts:
I need to install SQL Server MSDE 2000 SP4 on Windows 10.

I have other machines that run Windows 7 and 8.1 and SQL Server MSDE 2000 works fine (with merge replication configured too).

Now I testing the same scenario with Windows 10, but setup is closing unexpectedly during installation.

I know that this version is very old, but is there a workaround to install it on Windows 10?


texts: Trigger for updating a count of rows from a table into another table

texts:
I have these two tables:

mysql> select * from orders;
+-------------+---------+
| Customer_ID | Item    |
+-------------+---------+
| A01         | CPU     |
| A01         | Monitor |
| A02         | Monitor |
| A03         | UPS     |
| A02         | UPS     |
| A03         | CPU     |
+-------------+---------+
6 rows in set (0.00 sec)

mysql> select * from ordered_items;
+---------+-----+
| Item    | Qty |
+---------+-----+
| CPU     |   2 |
| Monitor |   2 |
| UPS     |   2 |
+---------+-----+
3 rows in set (0.00 sec)

I need to count each items in the orders table and update the Qty of the ordered_items table whenever the orders table is updated.

Is it possible to do with a trigger?


texts:
i'm trying to filter product with their subcategories :
for example :
if subcategories = (1,3,4)
and if this is subcategory_product :

how can i get the products with subcategories id=(1,3,4) ??!! plz help
in this case i should get the product with id 62 .


texts: PgPool setup with remote master and local read replica

texts:
I have working two server postgres setup with local (in docker) replica and remote (via VPN) master.
Without enabled load balancing everything (all queries) are redirected to master with enabled load balancing only 50% "read" queries are redirected to replica node.
Is there way to redirect all "read" queries exclusively to replica node?


texts: How to have different server config on replication server for postgres_fdw?

texts:
I have a postgres sharding based on postgres_fdw and postgres partitioning, lets say 3 machine (1 coordinator and 2 Shard machines). What I want to do is have read replica version of same? How should I do it?
What I have tried for now is, I have created read replicas of all the three machines but since server configuration also gets copied to the replica of coordinator server, there is no way for me to redirect queries from replica of coordinator to replica of shard machines? How do i do that?


texts: Separate Schemas by Industry or by Application functionality?

texts:
I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web based.

I might use Postgresql, I would go for a Single DB and shared schema. (The data volume is low and the mo. of concurrent users will be 400 max.).

The retail app addresses billing, inventory etc.,(backoffice/mini-ERP). The education app covers student enrollment, management and student prospecting etc.,(mostly Frontoffice - Student management, Knowledge Management, Leads, some Marketing etc.,)

The initial thought was to have a common public Schema (with addresses, contacts and other common table objects) and have specific Schemas for Retail and Education. i.e., Industry vertical -wise. As I add more customers in different businesses than existing I will end up adding additional schema. This could be one approach.

Or I was thinking should I go for a public schema and separate schema for ERP type functionality, CRM - like functionality etc., This way the schema will be Horizontal i.e., based on functionality rather than Industry. Tomorrow I can add a Chemical Retail or Medical contact management client or a Restaurant client who need billing and fit them easily into existing data models and can handle the variations in application code rather than creating new schemas for each business line.

Confused on which approach will be better in the long run?

EDIT: Or should I just not bother and stick with the public schema and handle variations in code? or are there any compelling reasons for the above approaches or any other better approaches than this?


texts: Advanced CSV export formatting in MS Access

texts:
I am trying to create a CSV file with the following format (for import into MYOB AccountRight financial software):

InvoiceNum1,Line1,...
InvoiceNum1,Line2,...

InvoiceNum2,Line1,...
InvoiceNum2,Line2,...

InvoiceNum3,Line1,...
....

So basically each invoice is separated by a blank line, and there can be one or more invoice items per invoice. I currently have a query written that does the following format (i.e. without blank lines):

InvoiceNum1,Line1,...
InvoiceNum1,Line2,...
InvoiceNum2,Line1,...
InvoiceNum2,Line2,...
InvoiceNum3,Line1,...

This query is a union query that brings together two normal sub-queries.

Unfortunately the software I am importing into is extremely stupid and treats this all as one invoice. So I need a way to insert the blank lines between each invoice. Before I export the CSV I am applying filters to the query (so I can select a date range) which means that any blank lines I manage to insert in my query get filtered out because they don't contain a date.

The solution I am currently planning is to write a separate program that the user can use to format the CSV file correctly before importing it into MYOB. This is obviously a huge hack and I would prefer a native Access solution if possible.

I'm using Access 2007, but can upgrade/downgrade to a different version if it gets me the functionality I need.


texts:
I have a table with just over a million records with a full-text index.

This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact same structure in a separate database at the end of every year. This second table has approximately 3+ million records.

I can only guess why this has been done, but now I've been asked to merge this two tables back into a single one, and partition it. I'm running SQL Server 2005.

Will the full-text search work in partitioned tables?

Are there any recommendations regarding this situation, or anything I should pay attention to?


texts: What does "slot 80 for LOB data type node does not exist" error in sql server mean?

texts:
When I modify a table in SQL Server 2008 I get the following error:

  The Database ID 29, Page (1:235225),
  slot 80 for LOB data type node does
  not exist. This is usually caused by
  transactions that can read uncommitted
  data on a data page. Run DBCC
  CHECKTABLE

Can any one help me?


texts:
I have MySQL  5.5.44 running in RHEL server. It is a standalone server used for testing and offlate not much used. The instance is currently down. Hence, I tried to start the service. It got started but everytime I check

  ps -ef | grep mysql

The process ID is getting changed and mysql instance is continuously getting restarted.

my.cnf entries:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log-bin=/var/lib/mysql/mysql-bin.log
log-bin-trust-function-creators=1
log=/var/lib/mysql/mysql.log
innodb_buffer_pool_size=3G
innodb_fast_shutdown=0
innodb_use_native_aio = 0
innodb_flush_method=O_DIRECT
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
port            = 3306

Error log entries:

InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 580582798, file name /var/lib/mysql/mysql-bin.000108
160709 12:21:49  InnoDB: Waiting for the background threads to start
160709 12:21:50 InnoDB: 5.5.44 started; log sequence number 166058394317
160709 12:21:50 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160709 12:21:50 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160709 12:21:50 [Note] Server socket created on IP: '0.0.0.0'.
160709 12:21:50 [Note] Event Scheduler: Loaded 0 events
160709 12:21:50 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.44-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
160709 12:21:50 mysqld_safe Number of processes running now: 0
160709 12:21:50 mysqld_safe mysqld restarted
160709 12:21:50 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
160709 12:21:50 [Note] /usr/sbin/mysqld (mysqld 5.5.44-log) starting as process 15180 ...
160709 12:21:50 [Note] Plugin 'FEDERATED' is disabled.
160709 12:21:50 InnoDB: The InnoDB memory heap is disabled
160709 12:21:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160709 12:21:50 InnoDB: Compressed tables use zlib 1.2.3
160709 12:21:50 InnoDB: Initializing buffer pool, size = 3.0G
160709 12:21:51 InnoDB: Completed initialization of buffer pool
160709 12:21:51 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 166055317403
160709 12:21:51  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 166058394317
160709 12:21:51  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

The above log entries is getting updated as and when instance is restarted.. the instance continues to restart every 4-5 seconds.

Help required here.!


texts: DTC Timeout when trying to update oracle table through link that contains a text index

texts:
Problem

Given is a SQL Server connected to an ORACLE Instance as a Linked Server. It’s using the Oracle Client OLEDB Provider. When doing an update in a transaction on a table having a CTXSYS.CONTEXT Text Index the DTC Connection aborts after approx. 10 Min with Error: 8522, Severity: 18, State: 1 on the commit. As soon you remove the CONTEXT index it works again.

Is this a bug in OraOLEDB or can we do something to fix this issue, while keeping the indexes?

Setup

On ORACLE

We use an ORACLE 11g 11.2.0.2.0

Create this table for this test.

CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));
INSERT INTO docs VALUES(1, 'California is a state in the US.');
INSERT INTO docs VALUES(2, 'Paris is a city in France.');
INSERT INTO docs VALUES(3, 'France is in Europe.');
INSERT INTO docs VALUES(4, 'Los Angeles is a city in California.');
INSERT INTO docs VALUES(5, 'Mexico City is big.');
COMMIT;
CREATE INDEX idx_docs ON docs(text)
    INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
    ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

On MSSQL

We use a SQL Server 2012 Express and Developer Edition both produce the same error.
Setup a linked server to above Oracle Server with Oracle Provider for OLE DB (OraOLEDB)  Release 12.1.0.1.0 Production.

Reproduce

Working Update

This updates work:

set xact_abort off;
update DBLINK..REMOTE_USER.DOCS set text='Mexico City is massive.' where id = 5;

(1 row(s) affected)

This Update Statement produces the error, after the DTC seems to run in a timeout.

set xact_abort on;
begin transaction t1
update DBLINK..REMOTE_USER.DOCS set text='Mexico City is huge.' where id = 5;
commit transaction t1;

(1 row(s) affected)
Msg 8522, Level 18, State 1, Line 4
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

Trace

This the end of the trace of a successful update:

This is the end of the trace of the update that runs into the timeout:


texts: A short way to execute a dynamic SQL string

texts:
I have a table, and a column with a query in it. And I want to update this table, converting the value to the result of it's execution.

This is a well known example of such table actually: all_tab_partitions. It has a column HIGH_VALUE with values like:

TO_DATE('2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS').

One could extract the date via substrings or regexps, but may be Oracle-guys think of exactly converting it via dynamic execution.


texts:
I am running this query to get all returns for a list of tickers:

select return1
from STOCK_DATA
where TDATE between 20130101 and 20131231
and TICKER in (SELECT DISTINCT TICKER FROM STOCK_DATA)

However, it stacks all returns for the tickers into one column, but I would like to have a table with return1 columns per each ticker?


texts: if block fails to create temp table in a procedure

texts:
I'm attempting to do this in a procedure:

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        SELECT ... INTO #bTemp FROM #aTemp;
    END
END

I get the error:

Msg 2714, Level 16, State 1, Line 10
There is already an object named '#bTemp' in the database.

Why is this happening and is there a work around?

Update

I've attempted to add a DROP statement as suggested here, but it still does not work:

DECLARE @a bit = 1;
BEGIN
    SELECT * INTO #aTemp FROM OPENROWSET( ... );

    IF @a = 0
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
    ELSE
    BEGIN
        IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
        BEGIN
            DROP TABLE #bTemp;
        END

        SELECT ... INTO #bTemp FROM #aTemp;
    END
END


texts:
I have 3 servers A, B and C. What I want to do is replicate databases like this A->B->C, where A is replicating DB1 and DB2 to B and B is replicating those same databases to C, but I would also like to add an additional database from B that will replicated to C.

In other words, I want to replicate databases like this:

A (DB1, DB2) -> B (DB1, DB2) + DB3 -> C (DB1, DB2, DB3)

Is this possible?


texts:
I would like to set up an index for a LONGTEXT column using:

CREATE INDEX post_meta ON wp_postmeta (meta_value(8));

There are currently ~1 million rows in the relevant table.

Questions:

Will creating this index affect the data in any way whatsover, for example, droping leading 0's or anyhting like that?
Is there any reason NOT to do this? There are many rows with content greater than 8 characters, but I frequently query on a type of entry that is 8 or less.


texts: PGpool Promote to master and Slaves wont replicate on failure

texts:
Sorry for the copy paste from stackoverflow, but this is probably a better place to post this.

  Version pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64.rpm

I’ve seen some chat online about this from a while back, but I haven’t found a solve.

I have 2 pgpool servers running streaming replication and load balancing. They connect to 3 postgres servers. 2 are virtual machines and 1 Physical machine.

I’d prefer the physical machine to be the master server. I have done test using the pcp_promote_node command, and I see that the show pool_nodes; shows the node I select as primary, however on postgres side the command SELECT pg_is_in_recovery(); show that server still in recovery.

All the pcp_promote_node command does, is detach master node. Which then prevents insert into the DB.

Secondly If the master fails, another server does become master, however the other slave is clueless of this and does not replicate any new data until I recover it from the new master.

Is there a work around for the above 2 problems. Also please let me know if u need more info, such as logs etc etc.

Thanks


texts: Unknown returned type in PostgreSQL query

texts:
The following query works:

SELECT a, b
FROM unnest(ARRAY[(1,2), (3,4)])
AS t(a integer, b integer);

a b
_ _
1 2
3 2

However, I wasn't able to use a different column type such as varchar(255):

SELECT a, b
FROM unnest(ARRAY[(1,'hello'), (3,'world')])
AS t(a integer, b varchar(255));

ERROR:  42804: function return row and query-specified return row do not match
DETAIL:  Returned type unkown at ordinal position 2, but query expects text.

It seems that, in the second case, the column type is inferred as unknown, which is not cast to varchar(255) automatically.

How do I make the second example work and return columns with the right type, if possible without warnings and without modifying the ARRAY[...] definition?

Background: I am trying to improve performance of large bulk insert operations using the psycopg2 Python module, which does not support using multiple rows in VALUES arguments. I stumbled onto the above example while trying out some other methods.


texts: Unfinished A Killed/Rollback Transaction

texts: I have killed a transaction 2 days ago but it hasn't finished yet. You can see the situation in the pic. What can i do?


texts: tmp_table_size with mostly InnoDB tables

texts:
I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two small Wordpress blogs. I wasn't trying to get too deep into optimizing this but I encountered what I consider to be strange behavior.

When I run MySQLTuner 1.5.0, I get the following output:

[OK] Currently running supported MySQL version 5.6.25-0ubuntu0.15.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics-------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 7M (Tables: 30)
[!!] Total fragmented tables: 3

-------- Performance Metrics -----------------------------------------
[--] Up for: 10d 13h 0m 49s (86K q [0.095 qps], 3K conn, TX: 218M, RX: 26M)
[--] Reads / Writes: 73% / 27%
[--] Binary logging is disabled
[--] Total buffers: 296.0M global + 1.1M per thread (75 max threads)
[OK] Maximum reached memory usage: 302.8M (15.20% of installed RAM)
[OK] Maximum possible memory usage: 380.4M (19.10% of installed RAM)
[OK] Slow queries: 0% (0/86K)
[OK] Highest usage of available connections: 8% (6/75)
[OK] Aborted connections: 0.34%  (11/3243)
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/109.0K
[OK] Read Key buffer hit rate: 96.7% (30 cached / 1 reads)
[OK] Query cache efficiency: 45.3% (27K cached / 60K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 81% (2K on disk / 2K total)
[OK] Thread cache hit rate: 99% (6 created / 3K connections)
[OK] Table cache hit rate: 76% (115 open / 150 opened)
[OK] Open file limit used: 0% (48/16K)
[OK] Table locks acquired immediately: 100% (41K immediate / 41K locks)

-------- InnoDB Metrics ----------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/7.4M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 15.19% (1244 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.93% (1295836 hits/ 1296742 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12926 writes)

-------- Recommendations ---------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)

My problem is the tmp_table_size (and max_heap_table_size). Every week I've been increasing it and MySQLTuner keeps suggesting larger. (As you can see, it now proposes >128M.) I can see from the above output that it's writing tables to disk, which I assume is the reason for the suggestion. However, in my understanding, this only relates to the MyISAM engine, while all of my Wordpress tables are using the InnoDB engine. I assume there are some internal MySQL tables that may be using MyISAM, but I still don't know why MySQLTuner thinks I need such a massive table size just for that minimal stuff.

So, the question: Why does MySQLTuner think I need such large temp tables, when I use only InnoDB?


texts:
According to the MySQL documentation, you can harden a MySQL server by adding passwords, or removing the anonymous accounts.

  If you want to prevent clients from connecting as anonymous users
  without a password, you should either assign a password to each
  anonymous account or else remove the accounts.

Before hardening, my users table looked like this.

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX |
| root             | gitlab    |                                           |
| root             | 127.0.0.1 |                                           |
| root             | ::1       |                                           |
|                  | localhost |                                           |
|                  | gitlab    |                                           |
| debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX |
| myuser           | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX |
+------------------+-----------+-------------------------------------------+

I've remove all anonymous accounts, so that the user table now looks like this.
(I'm using puppet to manage the users, but puppet effectively performs a DROP USER command).

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX |
| debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX |
| myuser           | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX |
+------------------+-----------+-------------------------------------------+

Why is it that I am still able to login to my test system without a username or a password?
What do I need to do to prevent any unwanted users from logging in?

root@gitlab:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)
....
mysql>

Update:
I've also just discovered that I can login as root, without entering a password.

Update2:
I found this question which has some good information, but does not solve the issue.

There are no anonymous users.

mysql> select user,host,password from mysql.user where user='';
Empty set (0.00 sec)

I login as root@localhost.

mysql> select USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

I do not have a default password, or skip-grant-tables defined in my.cnf

root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'skip-grant-tables'|wc -l
0
root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'pass'|wc -l
0

Update3:

I have tried performing these steps with puppet, (which should perform a flush privileges automatically). I have also manually flushed privileges and also tried restarting mysql.

Update4:
I've also tried changing the mysql root password and flushed privileges. No luck, I can still log in as any user without a password.


texts: Can atomic counter in trigger lead to "could not serialize access due to concurrent update"?

texts:
I have the following code:

create table users (id serial primary key, comments_counter integer default 0);
create table comments (id serial primary key, user_id integer references users(id) not null)

create or replace function users_comments_counter()
returns trigger as $$
begin
  update users set comments_counter = comments_counter + 1
  where id = NEW.user_id;
  return null;
end;
$$ language plpgsql;

create trigger users_comments_counter_trg
after insert on comments
for each row execute procedure users_comments_counter();

Is it possible that inserting a row to the comments table will throw could not serialize access due to concurrent update?

Or maybe in other words: is it possible to use such counter (with ANY of transaction isolation levels) while avoiding situation where "retry" (due transaction serialization error) is needed, and data consistency is guaranteed (counter will behave as expected - will be incremented only if comment row will be successfuly inserted).


texts: Why am I getting a syntax error when trying to create a stored procedure in MySQL?

texts:
I would like to use a transaction to call upon a stored procedure on many rows. If it fails on a single row, I want to roll back everything. Otherwise I want to commit.

I have the following so far but it throws the following error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near  'DECLARE val1 INT DEFAULT 0; DECLARE check INT DEFAULT 1;
  DECLARE done BOOL DEFAU' at line 3

 DELIMITER \\
 BEGIN

 DECLARE val1 INT DEFAULT 0;
 DECLARE check INT DEFAULT 1;
 DECLARE done BOOL DEFAULT FALSE;
 DECLARE e INT DEFAULT 1;
 DECLARE cursor1 CURSOR FOR SELECT Table1.col1 FROM Table1 WHERE Table.col2 = (SELECT col1 FROM Table2 WHERE col3 = 'xxx');

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 START TRANSACTION;

 OPEN cursor1;

 my_loop:
 LOOP

    FETCH NEXT FROM cursor1 INTO val1;
    IF done THEN
            LEAVE my_loop;
    ELSE
            storedProcedure1(val1,100,e);
            IF SELECT e = 0 THEN
                    ROLLBACK;
                    SET check = 0;
                    LEAVE my_loop;
            END IF;
    END IF;
  END LOOP;

  IF check = 1 THEN
       COMMIT;
  END IF;
  END;

Any ideas on what I may be doing wrong?


texts: Is it possible to create an user without create user command? I mean just by make an entry(framing insert query) in mysql.user table? Will it cause any crash in system tables?


texts: Can the LOG file fill up from index rebuilds where the indexes are not fragmented?

texts:
Working on SQL Server 2014.

I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been performing any transactions on the database for over a week. I asked the DBA if anyone else is using the database and they did not think so. The DBA mentioned that the automated index rebuild jobs could be filling up the log.

My question is this, can automated index rebuild jobs that run hourly fill up the log file? My assumption here is that the index would not be fragmented and so there would not be any work to rebuild the index. Therefor, the log file should not fill up. Is this assumption false? Am I missing something with how SQL Server index rebuilds work and how that might impact the log file?

Update

We are using Ola's solution: Click Here

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'\\DirectoryPath\',
@MirrorDirectory = N'\\DirectoryPath\',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 48,
@MirrorCleanupTime=24,
@CheckSum = 'Y',
@LogToTable = 'Y',
@Compress = 'Y'


texts:
So, In SQL, We have the following query to find the system data type ID of columns in SQL Server;

select name, system_type_id from sys.columns where object_id = OBJECT_ID(N'sometable')

It gives the following output;

name       system_type_id
rowid        56
Client       167
IsCurrent    104

The system_type_id represents the int value of data type of that column.

Whereas, when I tried to convert (find an equivalent) in MySQL, I couldn't find the ID of column's data type. The following is the query I have managed to find so far;

SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'someSchema' AND TABLE_NAME = 'someTable';

It gives the following output.

# column_name,   data_type
    'rowid',      'int'
     'Client',    'varchar'
     'IsCurrent', 'tinyint'

Any suggestions how I can achieve the earlier output in MySQL ?


texts: MySQL: Split column by delimiter & replace numbers with values from another column

texts:
Using MySQL, split column by delimiter & replace numbers with values from another column.

I have a column named 'path' having paths like '/426/427/428'.
I would like to replace category numbers with category names.
Result would be like '/Computers/Other accessories/Laser printers'.

This mysql query seems to be a good start, but don't know how to take column values from 'categories' table, 'path' column.

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', numbers.n), '/', -1) name
from
  (select @rownum := @rownum + 1 as n
        from categories
        cross join (select @rownum := 0) r
    ) numbers
where n > 0
order by
   n

Now this query splits the string correctly to:

426
427
428

Next would be to have a result like:

id    name
426   Computers
427   Other accessories
428   Laser printers

Finally should merge the 'name' column to

'/Computers/Other accessories/Laser printers'

string.

Thanks for your help in advance!


texts: What is the correct name for "relational role"?

texts:
If I want to describe a column as one of the following:

Surrogate key
Natural key
Foreign key
Attribute
etc.

then what do I call this list? Relational roles? I don't think "column type" quite cuts it due to confusion with "data type".


texts:
I'm currently working on a project integrating AD with SQL Server data, which I am
handling mainly via powershell (Invoke-SQLCMD in particular). The issue I'm running into is if someone has a special character in a field in AD, it's throwing an error when I'm trying to run my insert.

$OuListing = 'OU=FirstOU,DC=example,DC=com','OU=SecondOU,DC=example,DC=com'
$UserList = $OuListing | ForEach { get-aduser -filter * -SearchBase $_ -properties * -SearchScope 2 | where {$_.Enabled -eq $True} | Select CN,Surname }

$database = 'DB_Name'
$server = 'Server_Name'
$table = 'dbo.table_Name'

Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "Truncate Table $Table"

ForEach ($User in $Userlist)
{
    Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "Insert into $Table (CN,Surname) Values ('$($User.CN)','$($User.Surname)')"
}

This code works fine 99% of the time, but like mentioned, if an AD users last name is "Mc'Laggen", the apostrophe in it throws things off. Is there any good way to pass through the values like this to make it work, or should I be parsing through each attribute and add in escape characters where there's anything special? In the actual script, there are approximately 20 attributes so I was wondering if there was a simpler method of parsing through.


texts: PostgreSQL: PreparedStatement to execute set schema command

texts:
I need to execute in a Java application a SET schema ? statement, where the placeholder is replaced by a value provided at runtime by the caller.

I assumed I could use PreparedStatement to overcome potential SQL injection, but if I try to execute a prepared statement I get a PSQLException with message

ERROR: syntax error at or near "$1"  Position: 18

If I do execute the SQL by just concatenating the schema name by doing:

public void executeSetSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();
     stmt.execute("SET SCHEMA '" + schema + "'");

  } catch(Exception){}
}

Then everything works as expected.

Clearly, this happens with all the latest releases of PostgreSQL

Why I cannot use a PreparedStatement and what should I do instead to prevent SQL injection here?


texts: Oracle 11g - DROP database command on ACFS

texts: In Oracle 11g and higher (on RHEL 6) issuing the 'drop database' command in mount restrict mode will delete all the DB's ASM datafiles. Can somebody tell me if this command also drops all ACFS datafiles? The documentation only says it's doesn't drop datafiles on raw devices.


texts:
When developing a new system, for a big corporation.

Technologies:
Web, Java, Spring Boot, Rest Api, Postgres, Hibernate

Company:
Bank

System:
Promissory control, and all others

Company name:
Mycorp

Systems:
Many systems, dozens, dozens...

Is there a good reason to put the company name at the beginning of the table names?

For example, for the users table: mycorp_users

Sometimes I see something like this, the company name + the system name + ...: mycorp_systemx_users

Why not use a schema for this? dbmycorp.systemx.users

Or even not use anything only: someschema.users


texts: Mutually exclusive many-to-many relationships

texts:
I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, animals or bacteria, and each plant, animal or bacterium can be in an arbitrary number of containers.

So far this is very straightforward, but the part I'm having a problem with is that each container should only contain elements of the same type. Mixed containers that e.g. contain both plants and animals should be a constraint violation in the database.

My original schema for this was the following:

containers
----------
id
...
...

containers_plants
-----------------
container_id
plant_id

containers_animals
------------------
container_id
animal_id

containers_bacteria
-------------------
container_id
bacterium_id

But with this schema, I can't come up with how to implement the constraint that containers should be homogeneous.

Is there a way to implement this with referential integrity and ensuring on the database level that the containers are homogeneous?

I'm using Postgres 9.6 for this.


texts:
New to this stack exchange, I hope this is the correct one. So I am preparing for a final and one of our practice tests had us put locking on two transactions:

T1: R1(A), R1(B), W1(B)
T2: R2(B), R2(A), W2(A)

adding the locks is easy, you just prepend a RL or  WL before the transactions such that you get:

T1: RL1(A) R1(A), RL1(B) R1(B), WL1(B) W1(B)
T2: RL2(B) R2(B), RL2(A) R2(A), WL2(B) W2(B)

Where I get lost is that the solution in our practice states the schedule is going to be:

RL1(A) R1(A), T2: WAIT RL1(B) R1(B), WL1(B): WAIT; I assume after WL1 we also need W1(B). Now, from what I understand, in the above case we can only have two possible schedules: T1 -> T2, and T2 - > T1. This seems to imply T1 -> T2 -> T1. Why is that?


texts:
I have the following script for rman to backup the main database and archive logs:

connect target sysdba/password@localhost/db
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag Full_database_open format 'E:\backup\data\db_%Y_%M_%D_%t_%s_p%p.dbf' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET archivelog all format 'E:\backup\data\al_%Y_%M_%D_%t_%s_p%p.dbf' delete all input;
delete noprompt obsolete redundancy = 1;
backup AS COMPRESSED BACKUPSET current controlfile tag =cf1 format 'E:\backup\data\cf_%Y_%M_%D_%t_%s_p%p.dbf';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}

The data files are in: E:\APP\ADMINISTRATOR\ORADATA\DB\

I am trying to setup a test server, but installed oracle in the C:\ drive.
How, so the data file is: C:\app\administrator\oradata\DB

I tried the following:

-- sqlplus
shutdown abort;
startup nomount;

-- rman
restore controlfile from "C:\backup\data\CF_2019_08_27_1017362393_20_P1.DBF";

-- sqlplus
alter database mount;

-- rman
restore database;

unfortunately the backup tries to write data to the E:\ drive which is not present in the test server.

How can I make oracle restore the files to: C:\app\administrator\oradata\DB ?


texts:
I need help tuning mysql. Currently it is using a lot of CPU. I dont find any query that last longer than 3 seconds in SHOW PROCESSLIST and there are only around 20-25  running most of the time. Still the cpu usage goes beyond 600%

I have 24 GB RAM and 64 bit 8 core processor.

I got this results from mysqltuner but I cannot figure out what configuration change I need to do in my.cnf file.

my.cnf:

[mysqld]
max_connections=1000
innodb_file_per_table=1
innodb_io_capacity = 5000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_buffer_pool_size=10G
sort_buffer_size=2G
read_buffer_size=2G
join_buffer_size=2G
tmp_table_size=2G
key_buffer_size=3G
max_allowed_packet=160M
table_cache=1G
slow_query_log = 1
innodb_flush_method = O_DIRECT
query_cache_type = 1
thread_cache_size = 150
thread_concurrency=16
query_cache_limit=10M
query_cache_size=2G

Output of mysqltuner:

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 288M (Tables: 20)
[--] Data in InnoDB tables: 3G (Tables: 179)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 18

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 4m 46s (7M q [300.507 qps], 266K conn, TX: 12B, RX: 2B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 15.0G global + 6.0G per thread (214 max threads)
[!!] Maximum possible memory usage: 1299.1G (5769% of installed RAM)
[OK] Slow queries: 0% (1K/7M)
[OK] Highest usage of available connections: 85% (182/214)
[OK] Key buffer size / total MyISAM indexes: 3.0G/61.2M
[OK] Key buffer hit rate: 99.8% (4M cached / 6K reads)
[OK] Query cache efficiency: 79.2% (5M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 366K sorts)
[OK] Temporary tables created on disk: 21% (12K on disk / 57K total)
[OK] Thread cache hit rate: 99% (182 created / 266K connections)
[OK] Table cache hit rate: 22% (400 open / 1K opened)
[OK] Open file limit used: 9% (96/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 10.0G/4.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability

Please help me figure out how to improve cpu usage for mysql.


texts:
I'm working with an intranet application for property tax collection, and I've hit a snag.  The following view runs much slower than I'd expect:

create or replace view propertytaxessub0 as select
    propertysid,
    sum(amount) as totaltax
from taxes group by propertysid;

The "taxes" table is as follows:

create table taxes (
    sid                     int(10) unsigned not null auto_increment primary key,
    propertysid             int(10) unsigned not null,

    authority               char(30) not null default '',
    amount                  decimal(14,2) not null default 0,

    index (propertysid),
    index (authority)
) engine = innodb;

Here's a single-record lookup, and its EXPLAIN output:

mysql> select * from propertytaxessub0 where propertysid = 2;
+-------------+----------+
| propertysid | totaltax |
+-------------+----------+
|           2 |   121.97 |
+-------------+----------+
1 row in set (0.00 sec)

mysql> explain select * from propertytaxessub0 where propertysid = 2;
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows   | Extra       |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL |  53342 | Using where |
|  2 | DERIVED     | taxes      | index | NULL          | propertysid | 4       | NULL | 467217 |             |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
2 rows in set (2.07 sec)

I have no idea what I've done wrong here.  It's not a very powerful server, comparatively, but if you do this:

mysql> select sum(amount) as totaltax from taxes where propertysid = 2;
+----------+
| totaltax |
+----------+
|   121.97 |
+----------+
1 row in set (0.00 sec)

... it runs just fine.

I'm coming at this from the MS Access side of the house, where I created named queries all the time and used them this way to good effect.  What am I missing here?


texts:
I have two nodes and one database. I want to transfer data between them. DML operations on server node-1 should transfer to node-2 and DML operations on node-2 should transfer to node-1 as well.

Is this possible and what is the best (most fast, safe, and stable) way?


texts:
I want to sum a few values using case statements.  My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah.  For example, in my sample below this returns 2 entries for red12 even though 1 entry does not fit any of the criteria above, but the Group By messes me up!  How can I run this query and only return applicable results, i.e. what the case statements show?

Create Table #Bobsled
(
  [event] varchar(100)
  ,[time] decimal(18,4)
  ,employeeID varchar(25)
  ,name varchar(500)
)

Insert Into #Bobsled Values
('Walk', '16.32','red12', 'red arrow')
,('Eat', '5.12', 'red12', 'red arrow')
,('Run', '32.13','pink01', 'pink pig')
,('Walk', '2.12', 'bl81', 'blue fire')
,('Sleep', '8.12', 'gr99', 'green pony')

Select Distinct
employeeid
,case when [event] = 'Walk' then SUM([time]) else 0 end as walktime
,case when [event] = 'RUN' then SUM([time]) else 0 end as runtime
,case when [event] = 'Sleep' then SUM([time]) else 0 end as sleeptime
FROM #Bobsled
--only executes sucesfully with this
GROUP BY employeeID, [event]


texts: can you use msdb.dbo.sp_send_dbmail to set up calendar appointments?

texts:
For example, in health care, every employee gets a PPD test (they jab your arm with something) and you have to go back in 48 hours to see if you've sprouted an extra head.

Everybody forgets.

The nurses document in their EHR that they jabbed the patient, and I can send out an email reminding the employee to go check for their extra head, but I'd rather make it a calendar invite.

Question

How would you set up an outlook calendar appointment from SQL Server?


texts:
Background: I'm in the process of optimizing a Data Warehouse style query with big dimension tables. The optimizer does not like to pick the typical star schema join plan at the moment. When I force this plan (by using an index hint) I can see that this star join plan is just a little more expensive than the plan I'm using right now. I suspect that is because the dimensions do not fit into RAM on my development machine. The version is SQL Server 2014 but I hope that the question can be generalized to all recent versions.

Question: Is there a way to make the optimizer believe that it has a lot of RAM at its disposal? For testing purposes I would like to obtain the plan that it would generate if it had 1TB of RAM available (instead of 8GB at the moment).


texts:
I have some auto-generated T-SQL, which is probably valid, but I don't really understand.

ALTER TABLE [dbo].[MyTable]
WITH CHECK
CHECK CONSTRAINT [My_FORIEGN_KEY];

I know what a foreign key constraint is, but what's the CHECK CHECK?


texts: Fix the SQL Server R-Service (in-database) installation error

texts:
I have reset my windows 10 machine and tried to install SQL server 2016 developer edition. Everything else installed except the the R-Service (In-Database ). it has thrown the following error. But I have refreshed the machine last night, reset it with "Keep person files and folders" option, I'm using windows 10 home edition.
Error:

TITLE: Microsoft SQL Server 2016 Setup
The following error has occurred:
An error occurred while creating local user account MSSQLSERVER01. The account already exists.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=13.0.1711.0&EvtType=0xCE8B67C5%25400x92D6C0DE%25401392%25405

The link it provides is not helpful and its not directing to a knowledge base.
How do I fix this issue, do I have to uninstall the installed SQL Server and reinstall all again?


texts: Count distinct values of a column and insert them to another table

texts:
So, I have a database for logs and statistics. But, the amount of data sent is too high, so I was thinking of grouping the data into another table. So the structure of the original Database is:

ID  int
problem varchar(250)
channel_number  int
username varchar(50)
ip_address  nvarchar(50)
buffer_error_type varchar(50)
source int
Is_On   bit
logtype int
viewing_channel nvarchar(20)
playtime    int
date    bigint

where the field problem has values such as: "The User cannot connect to server","User's categories are 0","User's channels are 0","Buffer Error"(here  Buffer Error is specified by the field buffer_error_type as Warning or Error) etc. In the second table I would like to perform a insert select statement, which counts every single value of the field problem + specify the Buffer_Error value as Error,ignoring the Warning. So,the second table should have this structure:

username    varchar(50)
monthSel    bigint
CategoriesZero  int
ChannelsZero    int
BufferError int
BlackScreen int
OtherLogs   int
id  int

I do not know if something like this is possible, however, I tried this query:

insert into LogsArchive.dbo.ProblemsArchive (username,monthSel,CategoriesZero,ChannelsZero,BufferError,BlackScreen,OtherLogs)
(select (username),25114721, count(distinct problem),count(distinct problem),count(distinct problem),
count(distinct problem),count(distinct problem) from DbLogs.dbo.Logs
where username in (select distinct username from DbLogs.dbo.Logs)
 group by problem,username)

But I know I can't achieve the result with this.


texts: PostgreSQL 9.1 - Asynchroneous commits performance boost

texts:
During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the documentation of PostgreSQL that Asynchroneous commits might be an option for boosting performance when small data loss in case of a server crash is acceptable. We run the backend for an iPhone multiplayer game with quite simple, but a lot of, requests per second. Since it's a game and not a financial system we are running, the risk of data loss seems acceptable.

Does anyone have experience in difference in performance comparing synchroneous/asynchroneous commits?
Is there any risks with using this option, apart from data loss in case of a server crash?
Is there any risk that the queue of uncommitted transactions will keep growing since we will serve more requests?

Our setup
PostgreSQL, Nginx, Ruby on Rails, Unicorn


texts:
Does MSSQL Server need more space than the size of the data itself for importing? How much more? How to tell?

Recently tried copying data into MSSQL server (2012)

Microsoft SQL Server Management Studio          11.0.3156.0
Microsoft Analysis Services Client Tools        11.0.3130.0
Microsoft Data Access Components (MDAC)         6.2.9200.16384
Microsoft MSXML                                 3.0 5.0 6.0
Microsoft Internet Explorer                     9.10.9200.22387
Microsoft .NET Framework                        4.0.30319.18449
Operating System                                6.2.9200

using BCP and was seeing errors like

  Error = [Microsoft][ODBC Driver17 for SQL Server][SQL Server]The transaction log for database 'EpicShd' is full due to 'CHECKPOINT'.

and

  Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The transaction log for database 'EpicShd' is full due to 'ACTIVE_TRANSACTION'.

When looking at the DB itself, it threw...

  Database cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For context, the total size of the data (at least the TSVs that BCP was copying in) was ~10G, while the total storage space on the DB server was 28G, so I would think there would not be a problem. I am bringing in multiple tables each being 500MB-1GB and doing it in set of 1-2 at a time.

Does copying data into MSSQL server take more space than the data itself? How much (temporary storage)? How can I tell (not much experience with DBA)?


texts:
I have a trigger on insert but if the trigger fails the insert fails too.

is there a way to let the insert proceed even if the trigger fails?

EDIT: I use a trigger to send a email when a new record is entered. I want the record to be saved regardless if the email was sent or not..

How would I do that from a sp?


texts: SSRS 2016 issue - Web Portal URL is showing "Could not load folder contents "

texts: I need help for one if report server issue, We have migrated the SQL server from 2014 to 2016 using in-place upgrade. After upgradation we are having issue with web portal URL and it is showing "Could not load folder contents " error message while opening the url. We have already applied Service pack 2 on the SQL instance. Can someone help on this? Anyone faced such scenario. Please help


texts: Oracle: Trigger to store username after INSERT into column of same table

texts:
Let's say I have a column in a table within my database named 'CREATE_USER'.
How would I go about creating a trigger that on INSERT on the table: inserts the name of the row creating user into the column 'CREATE_USER' within that same row.

Basically a column that stores the row creating users username within the same row. I've searched around and could not find anything that worked for me. I included what I've tried below.

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT ON table_name FOR EACH ROW

DECLARE v_username varchar2(20);

BEGIN

    SELECT USER INTO v_username
    FROM dual;

    INSERT INTO table_name (first_col_name, second_col_name)
    VALUES (:new.first_col_name, v_username);

END;

The first problem I immediately run into is 'ORA-00947: not enough values'...

Also, if I wanted to do this for every table in my database, would I need separate triggers for each table?


texts: SQL Server Specific term full text search is not working

texts:
I'm trying to perform an specific term full-text search in SQL Server. It looks like the way to do it is by wrapping the keywords in double quotes, which I'm doing in my app:

Query = "\"" + model.SearchString + "\""

However, when I perform the query against the database with a term such as "foo bar", it returns every occurrence of "foo" and "bar" inside the database. The expected result is that the database should return only the records that have "foo bar" specifically. This is the query:

SELECT o.OpinionDocumentId
FROM CONTAINSTABLE(OpinionDocuments, Html, '"foo bar"') AS KEY_TBL
LEFT JOIN dbo.OpinionDocuments AS o ON o.OpinionDocumentId = KEY_TBL.[KEY]
WHERE o.Date BETWEEN @from AND @to

What could be the reason why this query is not returning the results from an specific set of keywords?

Thanks


texts: TSQL - create a function that takes a multi-row query result as an argument

texts:
I work with a variety of servers on mostly SQL Server 2014 and earlier.

I have boiler-plate code that can take the results of an arbitrary select statement (returning a list of varchar or the like) and transform them into a single comma separated string. I use this regularly on reports (for e.g. listing the agents associated with a single call).

I'm getting tired of retyping it.

How can I create a function (or stored procedure, or other thing I don't know about) to re-use this code neatly?

Ideal useage would be something like:

select @result = udf_makeCSF( (select [First Name] + [Last Name] from AgentTable agents where ... ) )

I've seen some suggestions about using exec for similar problems and that gives me the stone-cold horrors.

EDIT: To make it clear, I already know how to make a comma-separated list. That's the boiler-plate code which I'm getting tired of copy-pasting everywhere. So please don't get hung up on explaining that, that's a solved problem.

But to extend my example, to make it more concrete for folks:

If I have CallSegments table, like so:

pkey    callid    start time     duration    time to answer    handling agent   caller     ...
1       1         8:00:20        260         12                12               5551234    ...
2       1         8:04:35        380         3                 16               5551234    ...
3       2         9:08:16        512         36                12               5554321    ...
4       3         9:58:42        8           1                 12               5557890    ...

This table represents incoming activity at a call center. Each row is a single segment of a call - A single call may have multiple segments, such as when it is transferred to another agent or another queue, a conference call is started, etc.

This is simplifying the data I'm working with, it better represents the state of affairs halfway through my procedure.

I also have an Agents table, like so

pkey    first name     last name    hire date    ...
12      Alice          Smith        1900-01-01   ...
14      Bob            Jones        2000-12-31   ...

This represents individual call-center agents. The only information I need out of this (for this report) is names of handling agent(s)

I am tasked with producing a report on calls where I assemble the various segments into a single line per call summarizing the lifecycle of the call.

Example desired output would be something like this:

start time     duration    time to answer    handling agents           caller     ...
8:00:20        00:10:40    00:00:15          Alice Smith, Bob Jones    555-1234   ...
9:08:16        00:08:32    00:00:36          Alice Smith               555-4321   ...
9:58:42        00:00:08    00:00:01          Charley Carpenter         555-7890   ...

Collecting the statistics is a group by and a couple of SUM()s. Collecting the handling agents is not.

This is not my exact scenario every time, but it's variations on this depending on report requirements (agent names is common, but also what queues did this call go through, and similar). The similarity is having a column of data that needs to get concatenated with a separator (usually commas) and returned as a single string.

Which feels to me like making this a shared snipped of code to be re-used rather than retyped repeatedly is good. I would write a custom aggregation method in C# and be done with it, but most of the client sites I work at do not allow for this.


texts:
I have written a script to delete and create a proxy but getting the following error

Cannot delete proxy (7). It is used by at least one jobstep. Change this proxy for all jobsteps first.

USE [msdb]
GO

-- Drop the proxy if it already exists

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'CoreRefData')
BEGIN
  EXEC msdb.dbo.sp_delete_proxy @proxy_name = N'CoreRefData'
END
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'CoreRefData',@credential_name=N'CoreRefData',
        @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'CoreRefData', @subsystem_id=11
GO

EXEC dbo.sp_grant_login_to_proxy
    @login_name = N'INT\svc-w-corerefdata-de',
    @proxy_name = N'CoreRefData' ;
GO


texts: Automatic creation of unique Alphanumeric ID?

texts:
All the accounts in my database have a unique ID, that begins with the letter "G" (always), and has a 3 digit number following (e.g. "G026", "G123","G005","G999", etc.)

When a user is inputting new-account information through a form, I would like the unique ID to be auto-generated. So I would like a unique "G000" formatted number that is NOT already taken up in the list of users. I have tried a few things, but am having a lot of trouble getting it to work.

Does anybody have any ideas of a module I could implement? I'm thinking of making a module that generates these numbers, and calling this module in the "Default Value" field.

THANK YOU!!


texts: Setting up indexes to support a flexible API search endpoint

texts:
I have a MySQL 5.7 table that holds activities that users can search through. It will end up holding less than 100,000 rows and inserts will be less than 50/day. This activities table has a structure of:

CREATE TABLE activities (
    activityID int AUTO_INCREMENT,
    createdBy char, -- a unique AWS ID. eg: "us-east-1:128d0a74-c82f-4553-916d-90053e4a8b0f"
    active bool,
    city varchar,
    length int,
    price  decimal,
    latitude decimal,
    longitude decimal,
    CONSTRAINT Activities_PK PRIMARY KEY (activityID)
);

The queries I will be making are constructed depending on the query string sent to the API endpoint. There are 3 'paths' the API can be searched by to return activities.

If createdBY is included the query string, Select ... where createdBy = ~some id~
If city is included, Select by city, and length if included, and mininmum price if included, and maximum price if included
If latitude and longitude are included, search between the 2 points

Indexes I have come up with so far:

createdBy, as a B-Tree. Should this be a HASH index because it's a unique identifier?
(city, length, price). Since this is a covering index, if the constructed Select query is only searching by city, would it still use the index even though it contains length and price?

Also, since covering indexes start on the left, should I also include a (city, price, length) index?
(latitude, longitude)

Are these acceptable indexes? Should I include an index for the active column? All queries should only return active activities. My guess will be ~90% of rows would be active, while ~10% would be inactive. This answer suggests no.

Thanks so much! Any other suggestions are welcome!


texts: Convert a datetime column to number of seconds

texts:
In my SQL Server database, I have a datetime column.

What is a good way to create a new column that represents the long value for the datetime column? The long would represent a number of seconds.

I thought if I can convert it to longs, it would make it easier to do group by queries over time periods, as I could just divide the long number by fixed amounts.

The table is static, Won't be updating or deleting data.


texts: How to translate SQL Server stored procedure into Oracle stored procedure?

texts:
This is my SQL Server stored procedure which is below:

CREATE PROCEDURE flight_details
AS
BEGIN
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END

EXECUTE flight_details

The above SQL Server stored procedure worked successfully.

Next, I tried to translate it into Oracle stored procedure code which is shown below:

CREATE OR REPLACE PROCEDURE flight_details
(p_flight_details OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_flight_details FOR
SELECT Airline.Airline_No, Airline_Name, Airplane.Airplane_No, Airplane_Name, Flight.Flight_No, Flight_Type, City_Departure, City_Transit, City_Arrival, Departure_Date_Time, Transit_Date_Time, Arrival_Date_Time, Flight_Duration_in_hours, Transit_Duration_in_hours, Flight_Status
FROM Airline,Airplane, Flight
WHERE Airline.Airline_No = Airplane.Airline_No AND Airline.Airline_No=Flight.Airline_No AND Airplane.Airplane_No=Flight.Airplane_No
END flight_details;

SET SERVEROUTPUT ON;

EXECUTE flight_details;

I tried creating this procedure in Oracle SQL Developer but it was showing the following error:

Error(5,1): PL/SQL: SQL Statement ignored

Error(7,133): PL/SQL: ORA-00933: SQL command not properly ended

Error(8,19): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array


texts: Why can't user with SQLAgentOperatorRole delete his own SQL jobs

texts: I created a login and a user. I assigned the user as the db owner of the database "test". Then I granted him the role SQLAgentOperatorRole so that he can delete/create/modify the jobs belonging to the database "test". But I can only view the jobs though I have granted SQLAgentOperatorRole. What should I do to allow this user to do anything to the jobs belonging to the database "test" ? Please advice.


texts: PostgreSQL: Find row with JSON object attribute in array

texts:
I have a postgres database which is set up like this:

CREATE TABLE arr_test (
  id serial primary key,
  data jsonb
);

INSERT INTO arr_test (data) VALUES
  ('{"test_row": 1, "my_arr": [{"serial":"AAA", "content":"123"}, {"serial":"BBB", "content":"345"}]}'),
  ('{"test_row": 2, "my_arr": [{"serial":"CCC", "content":"456"}, {"serial":"DDD", "content":"567"}]}'),
  ('{"test_row": 3, "my_arr": [{"serial":"AAA", "content":"678"}, {"serial":"EEE", "content":"789"}]}');

I am trying to write a query where the data contains a list which has an object which contains a value. In this case it is the serial-field of the objects in the array that I want to query for.
For example, if I query for serial 'AAA' I want test row 1 and 3 to be returned, and if I query for serial 'EEE' I want only test row 3.

I have experimented with jsonb_array_elements and jsonb_to_recordset but am getting nowhere and would appreciate any help.


texts: How do you update one side of a MySQL master-master replication setup?

texts:
I have a master-master (circular) replication configured between two MySQL servers, call them West and East.  There is also a web app that interfaces with each DB, also on its own West and East server, such that the West web app talks to the West database and the East web app talks to the East database.  This all works fine during normal operations, but the problem arises when it comes time to do quarterly deployments.
In these deployments, almost always, there are database content updates and frequently there are database structure changes as well (e.g., adding a table, adding columns to a table, etc).  The web app is also updated at the same time so that it stays in sync with the database structure.
Thus far, we have not been able to perform a successful deployment without breaking replication, leading to a very messy situation and potential loss of data.  The way we planned to do deployments is as follows:

Shut down the West side web app and direct all traffic to the East side
Stop the East side slave
Deploy the West side web app + DB updates
Turn back on the West side web app, shut down the East web app, and direct all traffic to the updated West side
Start the East side slave
Deploy to East side web app + DB updates
Return to nominal

The issue seems to be around step 3.  When users are using the web app on the East side (thus modifying the DB) and the deployment is occurring on the West side (modifying that DB), this often stops replication from working, necessitating manual intervention, data copying, altering the pointers to the master logs, etc.  Is there a better way to do this to allow each master to be updated in isolation without risking replication getting out of sync?  I fear our setup is not correct and/or sustainable.
Important considerations:

There can be no downtime for the web app
The updates the users may be making while directed to one side must be preserved when both sides go back online

Also, please note that I am not a DBA, so I apologize if I failed to include any important details about the configuration settings.  I will try to update the question ASAP if needed.


texts:
We have a SQL Server database with two filegroups; PRIMARY and SECONDARY, with a single file in each file group.

All the clustered indexes are stored in the secondary filegroup. How can we remove the SECONDARY filegroup?


texts: Create a read only excel file from SSIS?

texts: Is there a way to create a read only excel file from SSIS?


texts: Update+Select in one SQL Server transaction, and concurrent deadlocks

texts:
I have a problem with a transaction isolation or concurrent atomic select+update.

SQL Server, C++ application, OTL/ODBC, and I can't use any stored procedures.

Here is the thing. The task is to update some field to a new value, and get (select) this value (it must be the last value, from update). However, there are several applications, which must do it concurrently.

My last code does something like:

SET IMPLICIT_TRANSACTIONS OFF
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

UPDATE tbl WITH (UPDLOCK, HOLDLOCK)
SET value = value + 1
WHERE name = some_name;

SELECT value
FROM tbl WITH (SERIALIZABLE)
WHERE name = some_name;

COMMIT TRAN

If we have two concurrent applications, which do the same thing (above) in rate 50-100 per sec, it leads to (~ 0.2% cases):

  OTL_EXCEPTION: 1205 : [Microsoft][ODBC SQL Server Driver][SQL
  Server]Transaction (Process ID 59) was deadlocked on lock resources
  with another process and has been chosen as the deadlock victim. Rerun
  the transaction.[ stm_text: update ...

SNAPSHOT for DB:

  SNAPSHOT ISOLATION is always enabled in this database.

Also there is the same results for SERIALIZABLE isolation level.

Is it possible to handle it? Currently, I have only one way - re-run query.


texts: Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

texts:
I am running into an issue where sensitive data was not encrypted properly at the application level of our production system.

Even though the guilty records have been removed or corrected, scanning still reveal the records in the .ibd file, as well as the ibdata1 and ib_logfiles.

I have optimized the affected table and so the .ibd file is clean now; but I still have the issue of the ibdata1 and log files.

Doing a full mysqldump of all databases is a last resort as the data set if quite large.  Since we are already using per table tablespaces, can I simply stop mysql, move the ib files and then restart and reset the files in that manner?


texts: How to change PostgreSQL IP address for streaming replication

texts:
I have Postgres replication (master & slave).

The slave server has 2 local IP addresses (192.168.1.17 & 192.168.2.12). As far as I know the streaming replication worked fine with slave using 192.168.2.12. But somehow the slave could not connect to master because it required another IP:

  2017-01-30 23:53:13.510 WIB >FATAL:  no pg_hba.conf entry for replication connection from host "192.168.1.17", user "user1", SSL off

I can put 192.168.1.17 in pg_hba.conf to work around this problem. I want to know if I did anything wrong to make the slave Postgres IP change? It was working just fine.


texts:
There are many similar questions about force index but mine is something different because i successfully use index at local dev server but can't on production server.

I have a table with more than 20 millions records MyISAM.

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `upload_id` int(10) unsigned NOT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `upload_id` (`upload_id`)
  KEY `for_where` (`user_id`,`amount`,`upload_id`),
) ENGINE=MyISAM AUTO_INCREMENT=20547623;

i need to run following query:

SELECT COUNT(DISTINCT `t`.`id`)
  FROM `cs` `t`
  LEFT OUTER JOIN `uploads` `Upload` ON (`t`.`upload_id`=`Upload`.`id`)
WHERE (((t.user_id=1) AND (upload_id IN (1,2,3)))
  AND (((Upload.term IS NOT NULL) AND (Upload.term<>"")) AND (t.amount>0)))

query took more than 20 seconds and EXPLAIN says that it "using where"

+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
| id | select_type | table  | type  | possible_keys             | key       | key_len | ref                | rows   | Extra       |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
|  1 | SIMPLE      | Upload | range | PRIMARY                   | PRIMARY   | 4       | NULL               |      3 | Using where |
|  1 | SIMPLE      | t      | ref   | user_id,for_where,upload_id | upload_id | 4       | mydb.Upload.id | 381427 | Using where |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+

but if i'll drop key upload_id or just add IGNORE INDEX (upload_id) it will use 'for_where' key and 'Using index condition' and query take only 1,5 sec.

+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
| id | select_type | table  | type   | possible_keys   | key      | key_len | ref                  | rows   | Extra                 |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
|  1 | SIMPLE      | t      | range  | user_id,for_where | for_where | 11      | NULL                 | 693667 | Using index condition |
|  1 | SIMPLE      | Upload | eq_ref | PRIMARY         | PRIMARY  | 4       | adrevyii.t.upload_id |      1 | Using where           |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+

it's good result for me so i try to roll out this on production server but no luck. It always say 'Using where' and still run more than 20 sec.

I was check for differences but can't find one. Both, local and production table use MyISAM, similar indexes and same data with one exception — production table have 2 millions records more.

My local server version: 5.6.13 Mac OS
My production server version: 5.1.69-0ubuntu0.11.10.1 (Ubuntu)

Why this happens? How i can force production server to use index? May be some server settings can affect it? Is it because version? Can it be fixed?
Is it possible to speed this query up more?


texts:
I want to make a trigger to record the time of any update as:

CREATE TRIGGER col_update
AFTER UPDATE ON col
FOR EACH ROW BEGIN
UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id
END

The problem is that when this trigger tries to update the updated column, it is also another update event, which runs the trigger. This will create an infinite loop, which does not work.

How can I store the update time in the corresponding column?

I wish to use a trigger because there are many columns in the table. If I try to set the update time manually, I would need to modify many queries.


texts: Cannot drop constraint due to FK references

texts:
I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the UNIQUE CONSTRAINT as it is referenced by 2 FKs, as in the following messages:

alter table checkpoint drop constraint unique_id_checkpoint;

ERROR:  cannot drop constraint unique_id_checkpoint on table checkpoint because other objects depend on it
DETAIL:  constraint fk_vehiculo_checkpoint_idpunto on table vehiculo_checkpoint depends on index unique_id_checkpoint
constraint fk_geocelda_punto on table geocelda depends on index unique_id_checkpoint
constraint fk_checkpoin_id on table ruta_checkpoint depends on index unique_id_checkpoint
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Query failed
PostgreSQL said: cannot drop constraint unique_id_checkpoint on table checkpoint because other objects depend on it
Detail: constraint fk_vehiculo_checkpoint_idpunto on table vehiculo_checkpoint depends on index unique_id_checkpoint
constraint fk_geocelda_punto on table geocelda depends on index unique_id_checkpoint
constraint fk_checkpoin_id on table ruta_checkpoint depends on index unique_id_checkpoint
Hint: Use DROP ... CASCADE to drop the dependent objects too.

As I've also got a FK on the same column (id), is it possible to switch those referencing FKs to the PK instead of the UNIQUE CONSTRAINT, so I can actually delete such constraint?


texts: Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5

texts:
I have two tables: hub and huboperator

hub table is defined as

CREATE TABLE public.hub
(
  parent_hub_id integer,
  ancestors jsonb,
  descendants jsonb,
  name character varying(1024),
  id integer NOT NULL DEFAULT nextval('hub_id_seq'::regclass),
 ....other fields
)

huboperator table is defined as

CREATE TABLE public.huboperator
(
  employee_id integer NOT NULL,
  hub_id integer NOT NULL,
  CONSTRAINT huboperator_pkey
    PRIMARY KEY (hub_id, employee_id)
)

"descendants" of hub is a jsonb array.

I have defined a hierarchy of hubs in the form of tree using parent_hub_id. All descendants of a hub are stored in the "descendants" array. An employee is assigned to a hub through huboperator table. If an employee is assigned to a hub, they can access that hub with all its descendants. To get the name of all the hubs which can be accessed, I use the following query:

SELECT hub.id, hub.name from (SELECT distinct
(jsonb_array_elements(A.descendents ||
jsonb_build_array(A.id))::text)::integer AS id FROM hub A
INNER JOIN huboperator on huboperator.hub_id = A.id
WHERE huboperator.employee_id = 1321)
AS all_hubs INNER JOIN hub ON hub.id = all_hubs.id

The explain analyze result of the query is:

Hash Join  (cost=18.18..310.29 rows=100 width=27) (actual time=4.945..46.369 rows=320 loops=1)
   Hash Cond: (hub.id = (((jsonb_array_elements((a.descendents || jsonb_build_array(a.id))))::text)::integer))
   ->  Seq Scan on hub  (cost=0.00..254.26 rows=9826 width=27) (actual time=0.010..18.717 rows=9826 loops=1)
   ->  Hash  (cost=16.93..16.93 rows=100 width=4) (actual time=4.913..4.913 rows=320 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 20kB
         ->  HashAggregate  (cost=15.41..15.93 rows=100 width=11) (actual time=3.582..4.214 rows=320 loops=1)
               Group Key: ((jsonb_array_elements((a.descendents || jsonb_build_array(a.id))))::text)::integer
               ->  Nested Loop  (cost=0.29..15.16 rows=100 width=11) (actual time=0.391..2.758 rows=320 loops=1)
                     ->  Seq Scan on huboperator  (cost=0.00..6.34 rows=1 width=4) (actual time=0.013..0.127 rows=1 loops=1)
                           Filter: (employee_id = 1321)
                           Rows Removed by Filter: 346
                     ->  Index Scan using hub_pkey on hub a  (cost=0.29..8.30 rows=1 width=11) (actual time=0.012..0.017 rows=1 loops=1)
                           Index Cond: (id = huboperator.hub_id)
 Planning time: 0.768 ms
 Execution time: 47.206 ms

Why is sequential scan performed on hub.id in the outer inner join?

If I turn off sequential scan through SET enable_seqscan = false, the analyze output is:

Nested Loop  (cost=20.12..472.61 rows=100 width=27) (actual time=2.789..7.873 rows=320 loops=1)
   ->  HashAggregate  (cost=19.84..20.36 rows=100 width=11) (actual time=2.756..3.311 rows=320 loops=1)
         Group Key: ((jsonb_array_elements((a.descendents || jsonb_build_array(a.id))))::text)::integer
         ->  Nested Loop  (cost=0.43..19.59 rows=100 width=11) (actual time=0.432..1.988 rows=320 loops=1)
               ->  Index Only Scan using huboperator_pkey on huboperator  (cost=0.15..10.76 rows=1 width=4) (actual time=0.045..0.048 rows=1 loops=1)
                     Index Cond: (employee_id = 1321)
                     Heap Fetches: 0
               ->  Index Scan using hub_pkey on hub a  (cost=0.29..8.30 rows=1 width=11) (actual time=0.018..0.023 rows=1 loops=1)
                     Index Cond: (id = huboperator.hub_id)
   ->  Index Scan using hub_pkey on hub  (cost=0.29..4.50 rows=1 width=27) (actual time=0.005..0.007 rows=1 loops=320)
         Index Cond: (id = (((jsonb_array_elements((a.descendents || jsonb_build_array(a.id))))::text)::integer))
 Planning time: 0.735 ms
 Execution time: 8.562 ms

Is there a way to use sequential scan in the query without turning off enable_seqscan? Can the query be restructured to make it faster?


texts: How should I design a re-usable content like table?

texts:
So I've got a database that holds articles, recipes, and comments in seperate tables, and I'm wanting to allow users to be able to like each of these content pieces (articles, recipes, comments).

I'm wondering whether it's a good idea to create a re-usable likes table which would look something like this:

| content_type | user_id | content_id |
| article      | 1       | 1          |
| article      | 1       | 3          |
| article      | 1       | 4          |
| comment      | 2       | 1          |
| comment      | 3       | 1          |
| recipe       | 2       | 1          |

So you'd have:

content_type:

which would represent either article, recipe, or comment

user_id:

which represents the user who liked the content

content_id:

this would be the primary key of the content piece (article, comment, recipe).

Is it a good idea to do this? There is the potential for millions of records to be inserted into the one table.

The alternative (which I am using right now) is that I have a table for each of the content types (articles, comment, recipe).

The thing is, I might be adding more content types to my database, which means the likes  schema I proposed might be a better idea.

Can anyone point out any cons of using a schema like this?


texts:
I have oracle ASM 11gR2 on oracle linux 5.8 and i want to install oracle database.
I have some disks and i want to use the redundancy strategies of oracle ASM or RAID 10 .
I do not know which one is better ? Oracle ASM redundancy or RAID 10 ?


texts:
This is my data:

I need a Fiscal Year to Date logic that will gather all previous months starting October.
However, for October 2018 I want to start form 15th, for remaining years it must start from October 1st.

Expected result would be all dates from October till last day of previous month.
For testing purpose I replaced sysdate, 'MM' with to_date('31-DEC-18'), 'MM'

Below is working but wondering if I can improve it...

PER_PERIOD_START_DATE between

(CASE
WHEN (a.PER_MONTH_NUM = 10 and extract( year from a.PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3) +14
ELSE Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3)
END
)
and trunc(to_date('31-DEC-19'), 'MM')-1

Final code:

where PER_PERIOD_START_DATE between

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3) + 14
ELSE Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3)
END
)
and trunc(sysdate, 'MM')-1


texts: Using something better than UNION for combining multiple queries

texts:
I have one big query which is based on 12 smaller queries that I have put together using UNION.

A relative simple example of my query:

(SELECT
    'type1' AS 'type',
     fieldOne AS someField,
     fieldTwo,
     fieldThree
     FROM tableOne
          JOIN users ON
               users.id = tableOne.id
     WHERE
          fieldFour = 'someValue'
)
UNION
(SELECT
     'type2' AS 'type',
     fieldOne AS someField,
     fieldTwo,
     NULL AS fieldThree
     FROM tableTwo
          JOIN users ON
               users.id = tableTwo.id
     WHERE
          fieldFour = 'someValue'
)
UNION ...

The query gets big since each of the 12 smaller queries need to have the same amount of fields, so I set many of the queries to NULL. This results in many reused lines throughout the queries. I must have the data in different tables in order to keep a good structure on things.

I think this can be made in a much more efficient way that is both smaller and takes less time to execute though I can't find any.


texts: How to detect any changes to a database (DDL and DML)

texts:
There are a lot of databases on my client's SQL server. These databases are under development, so developers can design, refactor, do data modifications and so on. There are some databases that change rarely. My client has to keep all of them safe (backed up) and spend some time managing the environment. (There is no DB administrator position at the company.) After lengthy discussion, the client has decided to use a daily full backup strategy, due to the ease of restoring.

So here is the summary of the situation:

Number of databases can vary every day.
Databases that were changed (meaning data and/or structure have been changed) shall be backed up.
Databases that were not changed shall NOT be backed up.
Solution shall not impact database structure (it's not restricted requirement)
This "backup engine" shall work automatically.

The main problem: how to detect that a database has been changed. The first part of the problem (DDL changes) can be resolved by using DDL triggers. But the data changes (DML changes) are a problem. It is impossible to apply DML triggers to all tables of all databases to track changes (performance, management of extended objects...). The backup engine has to track all changes to mark each database as ready to backup.

Change Data Capture is a solution but it seems too heavy (it requires SQL Server Enterprise Edition as well).
Another way is to track database file changes (size or last change time), but it does not work correctly: A database can change its size when it exceeds all reserved free space and sp_spaceused is not a solution.
Tracing is a solution but it causes performance issues and requires additional management.

Are there any solutions to calculate the actual database usage size without impact on other database management objects (like statistics..)? Granted that a change to a table's data that doesn't change the table's size would not trigger  (I think), but it's better than nothing. Really I am looking for a direct or indirect solution for SQL Server 2008.

Thank you for any comments, solutions, and thoughts.

ADDED:

Here is the solution (thanks to Marian):

Select
    NextLSN = MAX(fn.[Current LSN])
    ,Databasename = DB_NAME()
 from fn_dblog(NULL,    NULL) fn
     LEFT JOIN sys.allocation_units au
         ON fn.AllocUnitId = au.allocation_unit_id
     LEFT  JOIN sys.partitions p
         ON p.partition_id = au.container_id
     LEFT  JOIN sys.objects so
         ON so.object_id = p.object_id
    WHERE
    (
        (Operation IN
       ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
            'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')
            AND so.is_ms_shipped = 0)
        OR
        ([Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%')
    )


texts:
Folks from MongoDB have documented a very nice approach of using a data model for tree-like structures: they call it Materialized Paths.

Consider following:

If I populate this hierarchy using following code:

db.categories.insert( { _id: "Books", path: null } )
db.categories.insert( { _id: "Programming", path: ",Books," } )
db.categories.insert( { _id: "Databases", path: ",Books,Programming," } )
db.categories.insert( { _id: "Languages", path: ",Books,Programming," } )
db.categories.insert( { _id: "MongoDB", path: ",Books,Programming,Databases," } )
db.categories.insert( { _id: "dbm", path: ",Books,Programming,Databases," } )

I can easily retrieve, for example, all descendants of Programming:

db.categories.find( { path: /,Programming,/ } )

Now I'm considering of moving this database into Google Cloud Datastore, but I'm concerned about how painful would be to perform queries for such filtering? Can someone provide an example?

If it's relevant, backend is written in Go.


texts:
I'm wondering if anyone can shed light on a worry of mine that a query_plan_hash collision could cause a query to be executed as an entirely different query.

The hash is a 16 digit Hex that sp_help sys.dm_exec_query_stats says is a binary. Therefore it's only a 64 bit hash, and a collision seems very probable (considering SHA1 [160 bit] was just verified to have had collisions).

Would the plan_hash and the query_plan_hash both have to collide for this (query to be executed as an entirely different query) to happen?

I'm also curious if there is a setting somewhere in SQL Server that will allow us to change this hash to SHA2-512 (to reduce the possibility of a collision). Our data is very important.

I searched high and low via Google and Stack Exchange forums.


texts: Add values from two existing column into new columns for a total

texts:
We have two existing columns in our database named 'ClientIncome' and 'PartnerIncome'. We would like to add a new column named 'HouseholdIncome'

We would like to add the values from 'ClientIncome' and 'PartnerIncome' into the new 'HouseholdIncome' column?

Here is a small example of our database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] [float] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Incomes] ON

GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (1, 130.77, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (2, 294.82, 339.28, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (3, 0, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (4, 0, 396.82, NULL)
GO
SET IDENTITY_INSERT [dbo].[Incomes] OFF
GO

The table we would like to update has around 5 million rows, so any help with adding this new column would be appreciated.


texts:
is there any official or "de-facto" official implementation of Levenshtein distance on MariaDB 10.2 or 10.3?

I found this https://github.com/juanmirocks/Levenshtein-MySQL-UDF, but I want to be sure that there are no alternatives.


texts:
EDIT: Ok, so I managed to get something but it only changes to table on the left side even though I gave 2 specific left joins.

SELECT speler.Roepnaam as team1, wedstrijd.Speler2ID as team2
FROM wedstrijd
LEFT JOIN speler as player1
ON wedstrijd.Speler1ID = speler.ID
LEFT JOIN speler
ON wedstrijd.Speler2ID = speler.ID WHERE speler.ID


texts: mysql run out of memory during mview refresh

texts:
I want to create mview from mysql to Oracle using DBLink via odbc driver with Linux 64 bit os. but due to large number of Records around 2.5 cr records it raise the error

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][MySQL][ODBC 5.3(a)
Driver][mysqld-5.7.25-log]MySQL client ran out of memory (SQL State: HY000; SQL
Code: 2008)
ORA-02063: preceding 2 lines from dblink1

I am Using Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production .

mview Syntax :-

CREATE MATERIALIZED VIEW u1.T_VVS AS SELECT * FROM "m2"."m1"@dblink1

I know the question was repeated , but tried all things but result is zero that's why i posted my scenario.

Is there any solution for this ?

let me know if any extra detail required


texts: Storing multiple settings for multiple users in a SQL Server table

texts:
I'm using SQL Server 2012 to store the account settings for multiple accounts (each user account will have a set of settings). These settings could have any number of fields (has not been completely set and could grow) but each user account will have the same set of settings (different values of course).

Is it better (faster with a smaller footprint and more efficient) to:

give each setting its own column and each row to a user? keep in mind that there could be hundreds of settings variables. (i.e. userid, setting1, setting2, setting3...etc)
Relate each individual setting to a user and making it a name value pair table
(i.e.  userid, settingsName, settingValue)