texts: Insert Row With Foreign Key Equal to ID - Circular Reference

texts:
In MySQL database I have an events table which has a circular reference to itself with the following columns.

id (PK) | user_id (FK) | event_id (FK) | title

Each new row inserted has a circular reference to another row in the table. The problem is how do I create the first event for each user?

I have tried manually inserting the IDs so i know it is possible to have an event reference itself. For example if i send an explicit query with id = 1, user_id = 1, event_id = 1, title = test then the row is added fine and then adding other events for that user is then straightforward.

The problem is that this solution is only appropriate for testing. In production, when a user wants to create their first event, how can I construct a query so it sets the event_id to the same value as the new auto increment value on the id column?

To clarify, the first event for a user will throw a foriegn key constraint error as I do not know the event_id that should be set.

As requested

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  PRIMARY KEY
    (`id`),
  UNIQUE KEY `events_event_id_title_unique`
    (`event_id`,`title`),
  KEY `events_user_id_foreign`
    (`user_id`),
  CONSTRAINT `events_event_id_foreign`
    FOREIGN KEY (`event_id`)
    REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `events_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
  AUTO_INCREMENT=14
  DEFAULT CHARSET=utf8 ;


texts: I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a null for the sake of a join?


texts: How to allow for ampersand in XMLtable()?

texts:
Starting with this Q&A, note that while this works:

with data as (
  select 'Hallöle sloven&#264;ina Hallöle sloven&#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b

… this fails (note the addition of "AT&T and AT & T" to the string):

with data as (
  select 'Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b

with error message:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 2
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

How to fix?

First crude attempt(s):

replace('Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina', ' & ', ' and ')

While this works (at least for "AT & T", it would be desirable to preserve the ampersand in the final output…


texts: Prevent Subscription to report from running on a holiday

texts: I have a SQL Server that I am using for building and distributing reports.  I have built all the packages for importing data as needed as SSIS packages.  These reports only need to be run after the data has been imported.  I have built my SSIS packages to check and make sure that the day isn't a holiday by comparing the date to a table that contains holidays.  This way my data is only imported on business days.  The problem is, my report subscriptions are set to run at a specific time Monday - Friday.  How can I programmatically run the subscription at the end of my import, so that it only runs on the days that the import runs and not on the holidays?


texts: Measure CPU and Memory usage while running pgbench

texts:
what is the best way to capture the usage of CPU and memory while running pgbench? The only way I can think of is to read the info from top header.

what I do now

I run top command by print it out in a log file.
Then grep the cpu and memory in another txt file
Later I download the txt file and import it in excel file to get the average value of Cpu usage and Memory usage
The result of pgbench will be put together in the excel file

Is there any tips or standard of procedure to implement the TPC-B as well as monitor the resources.


texts:
I have a sproc that scripts out permissions and in the process, I add a check to see if the object exists before dumping the results into a table. Then, I have another re-assign permissions sproc that grabs row by row by using Cursor from the permission table and executes one by one. The performance with using a Cursor or without a cursor (run it directly from a query window in SSMS) are the same.
What is the quickest way to run these 42,000+ rows, with each having an IF EXISTS condition check?

Below are some of the permissions that gets executed:

IF EXISTS ( SELECT USER_NAME(grantee_principal_id)
            FROM sys.database_permissions
            WHERE class = 1
                and USER_NAME(grantee_principal_id) = 'TEST'
                and USER_NAME(grantee_principal_id) not in ('public'))
AND EXISTS (SELECT 1
            FROM sys.database_permissions
            WHERE OBJECT_SCHEMA_NAME(major_id) = 'dbo'
            AND OBJECT_NAME(major_id) = 'testtable')

BEGIN

Print 'Assigning permission GRANT UPDATE on dbo.testtable to [TEST]' GRANT UPDATE on dbo.[testtable] to [TEST] END;

IF EXISTS ( SELECT USER_NAME(grantee_principal_id)
            FROM sys.database_permissions
            WHERE class = 1
                and USER_NAME(grantee_principal_id) = 'ReadOnly'
                and USER_NAME(grantee_principal_id) not in ('public'))
AND EXISTS (SELECT 1
            FROM sys.database_permissions
            WHERE OBJECT_SCHEMA_NAME(major_id) = 'dbo'
            AND OBJECT_NAME(major_id) = 'testtable')

BEGIN Print 'Assigning permission GRANT SELECT on dbo.testtable to [ReadOnly]' GRANT SELECT on dbo.[testtable] to [ReadOnly] END;


texts: SQL Server 2012 - Disable Job On Linked Server

texts:
I have a job on server B, which is linked to server A. Is it possible to disable a job that exists on server B in the context of server A? I have tried the following command from server A and receive an error message.

EXEC SERVER_B.msdb.dbo.sp_update_job @job_name = N'test_job', @enabled = 0

Error message:

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('test_job') does not exist.

I also tried creating a stored procedure on server B that executes the above command. The procedure works when executing it from the context of server B, but not from server A. Is it possible to disable a job on a linked server?


texts:
We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I

we are planning archive inactive database.Is there any way we can check what databasese are inactive?

I have checked at the OS level file modification timestamp,but as we have so many database its taking time.Please let me know is there anyway we can check.

Note: Timestamp column is not present in any of tables


texts: How hard would it be to have PostgreSQL run a test to adjust the cost parameters?

texts: By using EXPLAIN one can see the cost of running a query. The cost is in artificial units, and they are based on pre-defined parameters. 1 is one disk fetch and a CPU cycle is 0.01 units, I believe. My question is why PostgreSQL does not automatically run a test upon installation to determine the real numbers based on the hardware? Shouldn't this be an obvious quick win?


texts: Not able to select the disk during Oracle 11g Installation

texts: Hi All, During the installation, I am not able to select the available disks which are already stamped using ASM. Any idea what could be the reason? I have tried to select using keyboard space as well but doesn't work out. Appreciate your help. Thanks.


texts: INSERT performance difference between temporary tables and table variable

texts:
I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.

This is the code to insert into the table variable

DECLARE @Data TABLE(...)
INSERT INTO @DATA( ... )
SELECT ..
FROM ...

This is the code to insert into the temp table

CREATE #Data TABLE(...)
INSERT INTO #DATA( ... )
SELECT ..
FROM ...
DROP TABLE #Data

The temporary table doesn't have any keys or indexes, the select part is the same between the 2 queries, and the number of results returned by the select is ~10000 rows. The time needed to execute the select alone is ~10 seconds.

The temp table version takes up to 10 seconds to execute, I had to stop the table variable version after 5 minutes.

I have to use a table variable because the query is part of a table value function, that doesn't allow access to temporary table.

Execution plan for the table variable version

Execution plan for the temp table version


texts: How should I interpret the "lock time" within mysql slow query log?

texts:
I'm trying to understand how to best interpret the lock time for the queries which show up on our MySQL slow query log.

For instance if an UPDATE query has 10 second lock time. I presume it is the total time after the update query acquires the lock. Even if it is waiting for previous select queries to complete but not executing the UPDATE action itself, the clock should be ticking since it is locking all the SELECT queries which have lined up after the UPDATE query.

And how about the SELECT query locks. How come some select queries have lock times? Is it because there is an UPDATE query following up hence they are locking a table together.


texts: PostgreSQL JSON query array against multiple values

texts:
I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups.

Given this example table:

CREATE TABLE grp(d JSONB NOT NULL);

INSERT INTO grp VALUES
   ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}')
 , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}')
 , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}');

I found similar question (PostgreSql JSONB SELECT against multiple values) and managed to achieve what I want on simple array using this query:

SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar'];

However, I can't make it work when when array contains JSON objects:

SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}'];

Here is what I expect from my query:

grp "First" -> customer "1"

grp "Third" -> customer "5"


texts: Reason for multiplicity of character/collation settings in MySQL?

texts:
The last 48 hours or so I spend on trying to determine the cause for sporadic crashes of my MySQL (Aurora) DB. Turns out they were attributed  to my mixing of database, table, column and stored procedure collation/charsets. My bad, or is it?

Aurora aside, what were the practical reasons for all the ways to configure charset/collation in MySQL? Why couldn't utf8 just become utf8mb4 as part of a major release? Why changing charset or collation of a database/table doesn't actually change the database or table, only future additions to the schema? The last one provides for an opportunity to have a mix of columns charset/collations in your tables, for instance. What sane person would want to have different charsets/collations within the same DB? And if there is a rationale for all this madness, why not make it the exception rather than the norm when configuring mysqld?

In my case, to resolve mysqld crashes I had to ensure all of below return consistent charset/collation values:

show procedure status where db = '<database>';
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "<database>";
SELECT T.table_name, CCSA.character_set_name
 FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "<database>"
;
SELECT table_name, column_name, character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "<database>";

Example crash stack trace which appears to be tied to my mixing of charset/collation types:

  mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=22
max_threads=2000
thread_count=18
connection_count=18
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1070055 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2a2a2a50d000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x2a2a2a50d000 thread_stack 0x40000
/rdsdbbin/oscar/bin/mysqld(my_print_stacktrace+0x2c)[0x960ccc]
/rdsdbbin/oscar/bin/mysqld(handle_fatal_signal+0x491)[0x6d7bb1]
/lib64/libpthread.so.0(+0xf5b0)[0x2b299b88d5b0]
/rdsdbbin/oscar/bin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderS5_S3_P13st_select_lexP18st_select_lex_unit+0x127d)[0x76e0fd]
/rdsdbbin/oscar/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x86b)[0x776eab]
/rdsdbbin/oscar/bin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x127)[0x777067]
/rdsdbbin/oscar/bin/mysqld[0x5ac17d]
/rdsdbbin/oscar/bin/mysqld(_Z30mysql_execute_command_internalP3THD+0x3c74)[0x7556d4]
/rdsdbbin/oscar/bin/mysqld(_Z21mysql_execute_commandP3THD+0x30)[0x757670]
/rdsdbbin/oscar/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x354)[0x768914]
/rdsdbbin/oscar/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0xb6)[0x768ad6]
/rdsdbbin/oscar/bin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x153)[0x768d73]
/rdsdbbin/oscar/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x12e4)[0x75a204]
/rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer7consumeEjj+0xc0)[0x7e5610]
/rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer5startEv+0xa0)[0x7e5720]
/rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer11drain_queueEPv+0x6a)[0x7e596a]
/lib64/libpthread.so.0(+0x7f18)[0x2b299b885f18]
/lib64/libc.so.6(clone+0x6d)[0x2b299e658b2d]


texts: Get max date for each year from list of dates

texts:
Need to get max date for each year from list of arbitrary dates:

date         year
=================
1/10/2013    2013
3/26/2013    2013
4/20/2012    2012
9/10/2012    2012
5/6/2011     2011

So with a working query, using the above data, the correct result would be:

maxdate     year
=================
3/26/2013    2013
9/10/2012    2012
5/6/2011     2011

Our database is proprietary, supporting only ANSI-SQL, no fancy extensions.
Tried using group_by, but it's retrieving all dates for each year, not what I want.


texts: ora-01410 invalid RowID sporadically in a select statement

texts:
I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this

SELECT distinct t1.crit
  FROM table_name1 t1
 INNER JOIN table_name2 t2
    ON t1.crit = t2.crit
 WHERE NVL (t2.qty, 0) + NVL (t2.adds, 0) - NVL (t2.remove, 0) > 0
   AND t1.process_id = 'some_process_id'
   AND t1.item_no = 'some item NO'
   AND t1.to_gen = 1

where t1 has an index on (item_no, crit), and t2 has an index on (item_no, crit, X), and X is some additional column.

The objective of this statement is to find all candidates for which I need to generate data in additional tables. When the new data has been generated, the column t1.to_gen is set to 0. At the moment it is not clear, if the software which runs this statement, does intermediate commits, e.g. generates some new data for one item_no, sets t1.to_gen = 0 and commits, before using the next item_no from the cursor of the statement in question.

This statement is executed successfully several times a day in my environment, but in rare cases I get an error from oracle, whose source is not clear to me:

ora-01410: invalid RowID

As the select statement does not use the pseudo-column RowID direcly, Oracle will have to use it internally.

What is the source of this problem?

Update: Sad as it sounds, the system does intermediate commits, but that is not the source of the problem.


texts: How to answer why suddenly we need indexes or query needs to be changed

texts:
I am junior DBA with 3 years of experience. Our job is to fine-tune queries or advise developers that particular code should be re-written or indexes are needed.

One simple question the dev team asks frequently is: "Yesterday it ran fine, what changed suddenly?" and we will be asked to check the infrastructure side. The first reaction on any problem always appears to be to put maximum blame on the infrastructure which is always the first thing being validated.

How should we answer to "what changed" questions by the development team? Did you guys ever faced the same situation? If so, please share your experience.


texts:
Is there a shorthand for merging rows based on the primary key? For example:

MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target
USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source
   -- The following line is pseudocode illustrating what we want to accomplish
   ON (target primary key equals source primary key)
WHEN MATCHED THEN
    UPDATE SET target.MyColumn = source.MyColumn
WHEN NOT MATCHED BY TARGET THEN
    INSERT (MyCol1, MyCol2, MyCol3)
    VALUES (source.MyCol1, source.MyCol2, source.MyCol3);

Writing out the conditions is tedious if the primary key contains a large number of columns. Also, if the primary key changes, many joins and merges might suddenly break.

You might ask, "Why is the primary key changing?" Real scenario: We were told that an object was uniquely identified by its distinguished name. Later on, we found out that a distinguished name could change if the object moved to another subnet. New requirements rolled in that changed how we uniquely identify the objects. This broke the product in quite a few places. Some of the mess could have been avoided if we had merged on primary key instead of on specific columns.


texts: Point in time recovery Oracle 12c with TDPO

texts:
I have to do a Point in Recovery to 2017-09-12:13:30:00. I'm running in to a TDPO/TSM error ANS1302E (RC2). List backup shows that the backup and archivelogs are available for restore. Anyone had that issue before?

rman target /
Startup mount
RUN {
   allocate channel t0 type 'sbt_tape' parms
   'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
   set until time "to_date('2017-09-12:13:30:00', 'yyyy-mm-dd:hh24:mi:ss')";

   restore database;
   recover database;

   release channel t0;

    }
connected to target database (not started)

Oracle instance started
database mounted

Total System Global Area    2399141888 bytes

Fixed Size                     8623496 bytes
Variable Size                671091320 bytes
Database Buffers            1711276032 bytes
Redo Buffers                   8151040 bytes

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: t0
channel t0: SID=135 device type=SBT_TAPE
channel t0: Data Protection for Oracle: version 8.1.0.0

executing command: SET until clause

Starting restore at 12-SEP-17

channel t0: starting datafile backup set restore
channel t0: specifying datafile(s) to restore from backup set
channel t0: restoring datafile 00004 to /opt/oracle/oradata/datafile/DBOLR2T/use
channel t0: reading from backup piece DB_954446866_1042_1
channel t0: ORA-19870: Fehler beim Zuruckschreiben von Backup Piece DB_954446866
ORA-19507: Fehler beim Abrufen der sequentiellen Datei, Handle="DB_954446866_104
ORA-27029: skgfrtrv: sbtrestore hat Fehler zuruckgegeben
ORA-19511: Kein RMAN-, sondern Media Manager- oder herstellerspezifischer Fehler
ANS1302E (RC2)    No objects on server match query


texts: Is there any tool to convert Oracle commands to MySQL?

texts: Is there any tool which I can use to convert Oracle commands to mysql commands?


texts: How to get SQL Server filetable path locator for subfolder by its literal path

texts:
I have a filetable which have several subtables.

For example

FileTable
->subtable1
->->subtable1.1
->->subtable1.2
->subtable2
->subtable3

etc

How to get SQL Server path_locator for subtable given by something like '/subtable1/subtable1.1' ?

I need it to move files from root to subfolder. I tried this, but its does not work

declare @parent hierarchyid;
set @parent = GetPathLocator('folder2/folder2.1')

update physical_files set path_locator = @parent.GetDescendant(NULL, NULL)
where stream_id = '494D5C8B-AC22-E411-A464-00259060BBB9';

SQL Server 2012 reports:

  Msg 33423, level 16, state 1
  Invalid FileTable path name or format.


texts: Is it a good idea to have too many named locks?

texts:
I am using MySQL as a data store and a Java App on the top. There are many state changes that happen, and I am seeing some concurrency bugs in there.

I wanted to know if I can use named locks (GET_LOCK) for each of those connections ? At a time it can have about 6-7k of named locks. Will it effect performance ?

Or should I explore something at the application layer like zookeeper distributed locks ?


texts: MySQL Cluster - 'ONLINE' is deprecated Please use ALGORITHM=INPLACE

texts:
I'm just testing MySQL CLuster 7.3.8. According to the documentation, after adding new Data Nodes I need to ALTER my tables to spread the data to the new Data Nodes.

So, I run:

ALTER ONLINE TABLE `world`.`city` REORGANIZE PARTITION;
OPTIMIZE TABLE `world`.`city`;

And get the message:

0 row(s) affected, 1 warning(s): 1287 'ONLINE' is deprecated and will be removed in a future release.
Please use ALGORITHM=INPLACE instead Records: 0  Duplicates: 0  Warnings: 1

That's fine. But my question is How do I use ALGORITHM=INPLACE

I've tried:

ALTER TABLE `world`.`countrylanguage` ALGORITHM=INPLACE REORGANIZE PARTITION;
ALTER TABLE `world`.`countrylanguage` ALGORITHM=INPLACE, REORGANIZE PARTITION;
ALTER TABLE `world`.`countrylanguage` REORGANIZE PARTITION, ALGORITHM=INPLACE;

But they all result in a Syntax error. I've checked the official documentation, but this isn't helpful. It tells you it's deprecated, but then continues to give examples using the  'old' (ONLINE) style.

I've searched high and low,but can't find any examples using the ALGORITHM=INPLACE. Can anyone tell me the correct syntax?

It's not an issue really, I just like to know, so I can use the correct syntax now, and avoid having to change things later.


texts: Granting Read access to Replica database (via data guard) but need to restrict the same login in production Database

texts:
We use Data Guard to replicate production Oracle database. We have a need to grant read access to replica DB and at the same time need to restrict that same login gaining read access to production.

Since the data guard replicates everything (including user/roles) is there any way we can apply this restriction to the production databases? Should I look into to database server inbound port restrictions? Any thoughts on this are greatly appreciated.


texts:
I used to label columns in my databases like this:

user_id
user_name
user_password_hash

To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped doing this.

What is an effective way of labeling columns in a database? Why?


texts:
Firstly, transposing is too specific a word for the kind of transform I want to do. I have a software that allows a technician to design custom forms for end users. From the form, I can see every entry on it (checkbox, textbox) is stored in a row. I have been asked to report on these forms, and the only way I can think of to make the data more usable is to wrangle it into a new table where each row represents one form entry.

Before:

Entry    FormElement    Value
1        Checkbox1      True
1        Textbox1       Joe Smith
2        Checkbox1      False
2        Textbox1       Jane Johnson

After:

Entry    Name          isMale
1        Joe Smith     True
2        Jane Johnson  False

Is there any other way to report on the data than what I am considering?
Should I use a stored procedure?


texts: How to divide a big SQL Query into small query

texts:
I am trying to traverse the whole file system. I am picking each and every file and then trying to insert it into the SQLite Database. Before inserting I am also calculating the checksum of each file. So I am only storing filename with path and its checksum. The problem is that SQLite can insert 500 enteries at a time.So the insertion process gets terminated if the sql query becomes greater than 500. I tried to run this code for the 400GB data which contains more than 500000 files. I want to divide that big sql query into the multiple parts. In which each part must contain atleast 490 enteries.Then maybe I am able to insert the data into the SQLite.

#! usr/bin/ruby
require 'digest'
require 'fileutils'
require 'colorize'
begin
    DBNAME = "structure.db"

    DB = SQLite3::Database.new(DBNAME)

    DB.execute "CREATE TABLE IF NOT EXISTS nix(file_name TEXT,file_checksum TEXT)"

    structure_data = Dir["**/*"]
    puts structure_data

    insert_query = "INSERT INTO nix(file_name, file_checksum) VALUES "
    structure_data.each do |file|
        if File.directory?("#{file}")
        puts "Directory => #{file}"
    elsif File.file?(file)
        checksum = Digest::SHA256.file "#{file}"
        puts checksum
            insert_query += "('#{file.to_s}', '#{checksum.to_s}'), "
            end
    end
    insert_query = insert_query.chomp(', ')
    insert_query += ";"
    DB.execute(insert_query)

end

If there is any other serverless database then please suggest.


texts: How to avoid lagging when enabling log-slave-updates?

texts:
Master:

48GB RAM
16-core
my.cnf: https://clbin.com/hlkUo

Slave:

64GB RAM
24-core
my.cnf: https://clbin.com/viLm0

Both are running 5.5.28.

For the incremental backup purpose, I need to enable the log-slave-updates option. But Seconds_Behind_Master keeps increasing immediately after restarting MySQL. A simple statement get... 2 minutes to updated on the slave at spare time. At the busy time, it seems that the Slave cannot catch up with the Master, so I have to comment it out and restart MySQL.

Is there any way to improve this?


texts:
I'm trying to import a .dmp file to my oracle RAC database using the following command:

C:\app\ptmp>impdp [user]/[password]@'DEVRAC-SCAN:1521/DB' DUMPFILE=UPG_E_7d291ff.dmp
 LOGFILE=UPG_E_7d291ff.log TABLE_EXISTS_ACTION=REPLACE

And I get the following output from oracle:

Import: Release 12.1.0.2.0 - Production on Thu Dec 17 09:43:13 2015

Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

I've looked around a little bit and some people (https://community.oracle.com/thread/3788748?start=0&tstart=0 && https://stackoverflow.com/questions/11878631/expdp-from-oracle-rac-environment) have said it could be a permissions thing so I checked permissions with:

C:\app\ptmp>icacls c:\app\oracle\admin\pldb\dpdump
c:\app\oracle\admin\pldb\dpdump Everyone:(R,W)
                                BUILTIN\Administrators:(OI)(CI)(F)
                                NT AUTHORITY\SYSTEM:(OI)(CI)(F)
                                PL\PLORACLE:(OI)(CI)(F)
                                DEVRAC1\Administrator:(OI)(CI)(F)
                                DEVRAC1\ORA_GRID_LISTENERS:(OI)(CI)(F)
                                BUILTIN\Administrators:(I)(OI)(CI)(F)
                                NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F)
                                PL\PLORACLE:(I)(OI)(CI)(F)
                                DEVRAC1\Administrator:(I)(OI)(CI)(F)
                                DEVRAC1\ORA_GRID_LISTENERS:(I)(OI)(CI)(F)

the oracle tnslistener runs under PL\PLORACLE user. <-- this is actually not true, see edits.

I used these commands to create/change the data pump directory:

echo create or replace directory DATA_PUMP_DIR as 'C:\app\oralce\admin\pldb\dpdump\'; >1_UPDATE_DUMP_LOC.SQL
echo grant read,write on directory DATA_PUMP_DIR to public; >>1_UPDATE_DUMP_LOC.SQL

They were ran using this command in a batch file:

sqlplus -S [user]/[password]@'DEVRAC-SCAN:1521/PLDB' <1_UPDATE_DUMP_LOC.SQL >1_UPDATE_DUMP_LOC.LST

The user that is running the commands has sysdba privileges for the database. So, with all this, I can't really understand why the import of the .dmp file isn't working.

EDIT:

The oracle tnslistener is actually running as local service rather than PL\PLORACLE as I thought. The OracleJobSchedulePLDB_1, OracleServicePLDB_1 and OracleVssWriterPLDB_1 run under PL\PLORACLE though. The rest of the oracle services run as Local System/Service


texts:
I need to perform some audit, when change of a securable ownership, such as

ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

happens in database.

Database scope DDL-trigger seems an appropriate mechanism for this purpose. In the documentation (section DDL Statements That Have Server or Database Scope) I see that there should be ALTER_AUTHORIZATION event.

However, when I'm trying to create appropriate DDL-trigger

CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION
AS
BEGIN
    PRINT 'Perform audit';
END

I'm getting error Msg 1084

  Msg 1084, Level 15, State 1, Procedure OnAlterAuthorization, Line 2
  [Batch Start Line 0] 'ALTER_AUTHORIZATION' is an invalid event type.

In sys.event_notification_event_types

SELECT type_name
FROM sys.event_notification_event_types
WHERE type_name LIKE 'ALTER_AUTHOR%';

there is no ALTER_AUTHORIZATION event, just

type_name
-----------------------------
ALTER_AUTHORIZATION_SERVER
ALTER_AUTHORIZATION_DATABASE

of them ALTER_AUTHORIZATION_SERVER does not suit obviously, and ALTER_AUTHORIZATION_DATABASE according to documentation

  Applies to the ALTER AUTHORIZATION statement when ON DATABASE is
  specified

So, the question is. Where is ALTER_AUTHORIZATION promised in the documentation? How can I catch change of a securable ownership in database?


texts: Always on viability/configuration for very specific purpose

texts:
We need to know the risks/pitfalls of a particular AlwaysOn configuration. We want an AlwaysOn configuration with the following requirements(below).

My question is: Is this a sound configuration or am I missing something vital or is there a linchpin I'm missing that invalidates this setup?

Source SQL Server:
Runs on virtual machine A
Pharmacy application connects to a primary database with full access

Target/Replica SQL Server:
Runs on virtual machine B
Microsoft access connects to replica(of above) database with read-only access to run lots of reports

Witness:
File share

Note from Microsoft white paper (we want to connect this way for both nodes--node\instance-name versus listener):

You can connect to the secondary replica using node\instance-name: Configure the primary replica to PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE) and use ‘ApplicationIntent=ReadOnly’ on your connection string for reporting workload. In this case, if you mistakenly connect your reporting workload to a physical node that is already running as a primary replica, the connection is denied.

Fail over:
Fail over is manual process. Should A go down, we'd manually bring up the replica server B as the main server--asynchronously for starters -- even though there would be some room for data loss.


texts:
This is from show engine innodb status

---TRANSACTION 4FF4, ACTIVE 43350 sec, OS thread id 6232 fetching rows
mysql tables in use 2, locked 2
480318 lock struct(s), heap size 46578104, 48647170 row lock(s), undo log entries 377
MySQL thread id 21, query id 172 localhost 127.0.0.1 root Sending data
update sorted_tc

left join ana on ana.TCKIMLIKNO=sorted_tc.TCKIMLIKNO

SET sorted_tc.ADI = ana.ADI,

sorted_tc.SOYADI = ana.SOYADI,

sorted_tc.ANAADI = ana.ANAADI,

sorted_tc.BABAADI = ana.BABAADI,

sorted_tc.DOGUMYERI = ana.DOGUMYERI,

sorted_tc.DOGUMTARIHI = ana.DOGUMTARIHI,

sorted_tc.NUFUSILI = ana.NUFUSILI,

sorted_tc.NUFUSILCESI = ana.NUFUSILCESI,

sorted_tc.ADRESILI = ana.ADRESILADI,

sorted_tc.ADRESILCESI = ana.ADRESILCEADI
TABLE LOCK table `rehber`.`sorted_tc` trx id 4FF4 lock mode IX
RECORD LOCKS space id 41 page no 4 n bits 400 index `PRIMARY` of table `rehber`.`sorted_tc` trx id 4FF4 lock_mode X
TABLE LOCK table `rehber`.`ana` trx id 4FF4 lock mode IS
RECORD LOCKS space id 13 page no 4 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
RECORD LOCKS space id 13 page no 5 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
RECORD LOCKS space id 13 page no 6 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
RECORD LOCKS space id 13 page no 7 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
RECORD LOCKS space id 13 page no 8 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
RECORD LOCKS space id 13 page no 9 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
RECORD LOCKS space id 13 page no 10 n bits 176 index `GEN_CLUST_INDEX` of table `rehber`.`ana` trx id 4FF4 lock mode S
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

Is it possible to figure out if this transaction is actually processing something or deadlocked?

I am using Maria DB 5.3 64 bit on windows 10.

I can provide additional info if required.


texts: How bad is query plan pollution (too many equivalent plans in cache)?

texts:
Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well.

I noticed some old modules of a system have non-optimal SQL queries, with multiple concatenated value strings instead of parameter placeholders. They do a polling on a table, like every 10 seconds, to get items added during the last few minutes, which generates a new query plan on every execution.

Their performance is not too bad, no SQL injection risk (no web/user forms), they're old and it would be a lot of work to change their queries to correct parameterization. I suggest to do this change, but there's debate that it would be a waste of time, with other things being more important.

Edit: The database is supposed to run with mostly parameterized queries (which all newer modules use), so I would like to avoid the "optimize for ad hoc" option. Partially parameterized queries create a plan anyway. Is there a downside when running in ad-hoc optimized mode, with mostly parameterized queries?

To me, it seems like these old modules take a huge portion of the database resources, although they are few. Even a single module of this kind would create thousands of query plans over time, while all newer modules together have less.

Is it importante to change these, or can I leave them in their state, with optimization/parameterized queries only in current/future modules?

SQL is like:

select ItemId, ItemName from Items
where ItemType=3 and ItemCreator=1234
and ItemDate >= '2013-11-23 12:30:00'

where the values vary and the date is a few minutes before now. In a few cases, the date has been changed to a parameter like "@startDate", to avoid format problems, but ItemType and ItemCreator values are still concatenated strings.

When monitoring query plans with DMV or Activity Monitor (Recent Expensive Queries - Plan Count column), I notice some of these queries have 8000+ equivalent query plans in cache:

select count(*), query_plan_hash
from sys.dm_exec_query_stats
group by query_plan_hash
order by count(*) desc

then selecting the plan XML with a CROSS APPLY on sys.dm_exec_query_plan, with plan handles selected by query plan hash.

Edit/Temporary conclusion:
It seems like it is best to leave the very old applications as they are, even when creating tons of ad hoc queries. My greatest fear was that the flood of single use ad hoc queries would cause the good, multi-use parameterized and prepared query plans to be evicted from cache. This does not happen, because, when cleanup is done, ad hoc plans are evicted first, and others are rated by factors like complexicity, number of usages etc. So parameterized queries with a high usage rate will likely be kept, no matter how many ad hoc or partially parameterized plans flood in. Ad hoc optimization reduces plan size (actually, no real  plans are stored at first use), but even more plans may be kept, with a similar memory usage (is this correct?). Even partially parameterized SQL (DateTime parameters to avoid local format troubles) will quickly get evicted if not used again, even when sent with sp_executesql, which forces parameterization and plan caching. Having a huge number (5000 to 8000+) equivalent ad hoc query plans is no good, but probably less harmful than having to dig through years old C#, C++, maybe even Visual Studio 6.0 code, to fix queries (nobody pays for that, and the stuff is still running without recognizable problems).


texts: SQL Server is started but didn't listen on any port

texts:
I newly installed SQL Server 2012. No database created yet. And I can open the Management Studio with sa/password. The Binn\sqlservr.exe is started as a service and I can see it started in the service list. However, it didn't listening the 1433 port, or even, not listening any port by default, as I checked as below:

tasklist|find /I "sql"

I got:

sqlservr.exe                  5668 Services                   0     40,112 K

in which 5668 I think is the PID. And then to get PID = "5668" is listening to which port:

netstat -ano | find /I "5668"

but I got nothing except blank. On the other hand, after searching posts in this site, I double-checked my SQL Server's configuration: start->All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager-> SQL Server SQL Native Client 11.0 Configuration -> Client Protocols -> TCP/IP ->Default Port 1433. There're 3 services, SQL Server(MyInstanceName), SQL Server Agent(MyInstanceName) and SQL Server Browser. The last two is stopped.

SQL Server Configuration Manager-> SQL Server Network Configuration->Protocols for MyInstanceName -> TCP/IP is disabled by default, after I enabled it and restart the service and repeat the netstat -ano | find /I "newPIDNum" command I found the

TCP    0.0.0.0:52395          0.0.0.0:0              LISTENING  5668(newPIDNum)

Why 52395 instead of 1433? And I failed to create the DSN using Windows ODBC Data Source administrator which returns me an error: specified SQL server not found.
Any help for me? Thanks.


texts: Mysql Master-Master Replication Topologies on >2 machines

texts:
I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote.  Let's call my main server A, the secondary server B, and a remote, development server C.

As a possible configuration, I started reading about a ring configuration.  Some comments seem to look down on this type of topology.

My question is, given the current state of Mysql 5.1, what types of master-master topologies are advisable and known to be fault-tolerant?

Is a ring really a bad idea, in general?

In my case, A and B are on an intranet that isn't expected to have any network failures so B can theoretically be used as a failover for A.  Most of the time, I am working on C, a remote machine, which sometimes gets network hiccups in reaching A and B.  None of the data is currently critical (ie as long as they eventually get mirrored, it's fine), but I expect A and B to be mirrored tightly - within say a 5 second timeframe on light loads (I think this should be an easy requirement given my preliminary tests).

I currently have B as a Slave to A, but I am looking forward to going Master-Master on A, B, and C.


texts: Remove duplicates from different columns

texts:
I'm trying to remove duplicates from a set, but the duplicates are in different columns, so for example with this table:

ColA, ColB, ColC, ColD
----------
1,  1, 'ABC', 'DEF'
----------
1,  1, 'DEF', 'ABC'
----------
1,  1, 'GHJ', 'LKJ'
----------
1,  1, 'LKJ', 'GHJ'

What I need to end up with is:

ColA, ColB, ColC, ColD
----------
1,  1, 'ABC', 'DEF'
1,  1, 'GHJ', 'LKJ'

Hope that makes sense, does anyone have any ideas?

This is SQL-Server 2012.


texts: ora-12514 Error While Connecting local Oracle EXPRESS EDITION 11

texts:
I have recently installed Oracle XE 11 on my local machine and then installed SQL Developer. But when I try to create a new connection (for my localhost) or add from 'database detected local XE' it returns either ora-12514 or ora-12505. I know the password that I entered during the installation.

OracleXETnsListener,OracleServiceXE  service is running
Tried connecting via localhost, 127.0.0.1 and computer_name
Opened localhost tns and wrote the information there

But I am still not able to connect.

It bugs me that even a simple localhost connection is problematic when it comes to Oracle tools. It should be as easy as using Microsoft tools. I can't even connect to a localhost xe oracle.

What is wrong?

Edit: I may have found the issue.. I have installed Oracle XE 11.2 as domain user so probably there is some other configuration that needs to be done to access.?


texts:
I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.

I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows  from selected tables across the 8 source servers into a single instance of those tables on the reporting server. This is one-way replication (no changes will be made to the reporting server). I will need to replicate changes from the source databases with relatively low latency (say 20-30 seconds).

Furthermore, I'd like to find methods to achieve this with as little impact to the source servers as is practical.  3rd-party agents, triggers, or schema mods to those servers are difficult in my environment.

My questions:

What are promising architectures and technologies for  achieving this goal?
I've looked at SQL Server Merge Replication, but I am concerned about latency. Is this an appropriate technology for this goal?
Are there many-to-one architectures for transactional replication?
Should I be looking at 1-to-1 replication into 8 databases on my reporting server, followed by some custom merge function (a 2-step replication)?

Thanks,
John


texts: Consistent backup of large DB --single-transaction

texts:
I need to mysqldump a large DB (330 GB) in order to set up a slave for replication.

Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the duration?
If I put a read lock on, can I get my bin information but then can release the lock and dump using --single-transaction so there is minimal downtime to users?


texts: Any reason not to add 'rowversion' column?

texts:
The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access.  From the Supporting Concurrency Checks section:

  Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

The nice thing is that every UPDATE/DELETE statement and bound form will benefit from this addition without having to make any changes within MS Access (aside from re-linking the tables).

I was unaware of this feature until recently.  I am considering adding a named rowversion column to every table in SQL Server that I link to from MS Access.  Are there any downsides I should be aware of before I do this?

Obviously there will be storage requirements and performance impacts, but I assume these will be negligible.  Also, several of these tables are articles in a merge replication scenario; does that make a difference?


texts: Sync data near real time between 2 databases on 2 different servers

texts:
One of my clients asked me to find a solution that can sync the 2 databases in near real time. Basically, they try to pull the data through linked server from server A through server B, but since server A goes down they can't pull the data as the database is not available, so they need some near real time data available to be queried even if the source is not available for sometime. Also, something that I forgot to add the database should always be on server A.

SQL version - SQL 2012 SP3 (both server A and server B)
RAM - 32 GB

So essentially, Server A's database is the production DB, and Server B's database is used as a "read-only" copy - no changes go through server B.  If Server A goes down, then Server B is up-to-date, or almost so.  However, Server B is not intended to become the live production DB at that point - it's just available to reference the data until Server A comes back up


texts:
I have a table with the following columns:
filename, filetype, version and revision.

I want to code a query that will find the highest version AND revision for a filename and filetype. For example, the following will work:

select
 filename
,filetype
,max(version + revision) as combined
from dmkr_asline.cmires_lbyi
group by filename, filetype
order by filename, filetype;

However, I want to be able to return version and revision as separate columns so I can join another table. I cannot use max on each column because I want the max revision of the max version.


texts: Postgres Streaming Replication lagging, using lots of CPU and little I/O

texts:
We have 2 dedicated DB Servers. One is master and other is its streaming slave.
Postgres version 9.0.17.

We have similar hardware on both servers and using 15krpm SAS drive in Raid 10 for Database cluster. The only difference is that on server we have 128 GB ram and on slave we have 64 GB ram.

The issue which we are facing is that our streaming slave is lagging behind.

On the streaming slave we have

postgres=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location
-------------------------------
 1F7D/DD3BA000

postgres=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 1F75/5BE08980

This difference between wal received and replay is increasing constantly.

Please let me know that how this issue can be resolved.

Output of perf top -u postgres is

98.30%  postgres          (.) DropRelFileNodeBuffers
0.20%  postgres          (.) 0x000000000008b05b
0.14%  (kernel)          (k) copy_user_generic_string
0.13%  postgres          (.) hash_seq_search
0.07%  postgres          (.) hash_search_with_hash_value
0.05%  (kernel)          (k) _spin_lock
0.05%  (kernel)          (k) apic_timer_interrupt
0.03%  (kernel)          (k) do_timer
0.03%  postgres          (.) RememberFsyncRequest
0.03%  (kernel)          (k) __sb_start_write
0.02%  (jbd2)            (k) do_get_write_access
0.02%  (kernel)          (k) unroll_tree_refs
0.02%  (cciss)           (k) do_cciss_request
0.02%  (ext4)            (k) ext4_check_dir_entry
0.02%  (kernel)          (k) path_walk
0.02%  (kernel)          (k) scheduler_tick
0.02%  postgres          (.) LWLockAcquire
0.02%  (kernel)          (k) dyntick_save_progress_counter
0.02%  (kernel)          (k) _spin_lock_irqsave
0.02%  (kernel)          (k) audit_syscall_entry
0.02%  (ext4)            (k) ext4_journal_start_sb
0.02%  (kernel)          (k) radix_tree_range_tag_if_tagged
0.01%  (kernel)          (k) sys_getppid
0.01%  (kernel)          (k) native_read_tsc
0.01%  libc-2.12.so      (.) _IO_str_init_static_internal
0.01%  (kernel)          (k) __getblk
0.01%  (kernel)          (k) run_timer_softirq
0.01%  (kernel)          (k) system_call
0.01%  postgres          (.) __errno_location@plt
0.01%  libc-2.12.so      (.) __GI___libc_lseek64
0.01%  postgres          (.) XLogReadBufferExtended
0.01%  (kernel)          (k) memcpy
0.01%  (kernel)          (k) irq_work_run
0.01%  postgres          (.) fsm_set_avail
0.01%  (kernel)          (k) kmem_cache_free
0.01%  (kernel)          (k) do_unlinkat
0.01%  libc-2.12.so      (.) vfprintf
0.01%  postgres          (.) hash_any
0.01%  (kernel)          (k) sysret_check
0.01%  (kernel)          (k) mutex_lock
0.01%  (kernel)          (k) printk_tick
0.01%  postgres          (.) elog_start
0.01%  (kernel)          (k) __percpu_counter_add
0.01%  (kernel)          (k) file_update_time
0.01%  (kernel)          (k) enqueue_task_fair
0.01%  (kernel)          (k) blk_recount_segments
0.01%  (kernel)          (k) rcu_check_callbacks
0.01%  (kernel)          (k) put_pid
0.01%  postgres          (.) mdunlink
0.01%  postgres          (.) LWLockRelease
0.01%  (kernel)          (k) current_kernel_time
0.01%  (kernel)          (k) perf_event_task_tick
0.01%  (kernel)          (k) native_apic_mem_write

output of gdb for postgres startup process

(gdb) thread apply all backtrace Thread 1 (Thread 0x7fd06d28f7c0 (LWP 26949)):
#0  0x00000000005ec320 in DropRelFileNodeBuffers ()
#1  0x0000000000603115 in smgrdounlink ()
#2  0x000000000047d992 in ?? ()
#3  0x000000000048eca9 in StartupXLOG ()
#4  0x0000000000490c58 in StartupProcessMain ()
#5  0x00000000004a6faf in AuxiliaryProcessMain ()
#6  0x00000000005d31c3 in ?? ()
#7  0x00000000005d7372 in PostmasterMain ()
#8  0x000000000057e320 in main ()


texts: Replication with Global Transaction Identifiers across different MySQL versions

texts: I have a master MySQL server running 5.0.67. Would it be possible to configure a GTIDs replication as a slave that runs 5.7.17 that connects to this master? I have tried the traditional binary log position replication and have no issues.


texts: Why a query is not covered when using a range operator on documents?

texts:
I have created the following 1000 documents:

for(i=0;i<1000;i++){db.doc.insert({"doc":{"k1":"v_"+i,"k2":i},"scal":i})}

So a document looks like this:

var d1 = db.doc.findOne()
{
    "_id" : ObjectId("5ce25f8920b0be2428648e38"),
    "doc" : {
        "k1" : "v_0",
        "k2" : 0
    },
    "scal" : 0
}

I've created the following two indexes:

db.doc.createIndex({"doc":1})
db.doc.createIndex({"scal":1})

I wonder why the following query is not covered:

db.doc.explain(true).find({doc:{$gt:d1.doc}},{_id:0,doc:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 999,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 999,
        "totalDocsExamined" : 999,
        ...
}

However, the same query using a scalar instead of a document is covered:

db.doc.explain(true).find({scal:{$gt:d1.scal}},{_id:0,scal:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 999,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 999,
        "totalDocsExamined" : 0,
        ...
}

I only get the query covered on documents, when I'm not using a range operator:

db.doc.explain(true).find({doc:d1.doc},{_id:0,doc:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 0,
        ...
}

Even $in would result in a covered query:

var dis = db.doc.distinct("doc")
db.doc.explain(true).find({doc:{$in:dis}},{_id:0,doc:1})
{
...
"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1000,
        "executionTimeMillis" : 8,
        "totalKeysExamined" : 1000,
        "totalDocsExamined" : 0,
        ...
}

However range operators such as $gt seem not to be able to result in a covered query when applied to documents. Is this an expected behaviour which is documented anywhere or is it a bug?

I'm using a replSet running with mongodb v3.6 Linux 64 Bit.


texts: SELECT w/ INNER JOIN Query on Optional Columns

texts:
I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables.

File2 is all about the educational background of an employee. The employee has to select from a dropdown list in the site the education establishment first, e.g. Highschool, College, etc. Branch of Study refers to the course the employee has taken, e.g. IT, HRM, Photography, etc.

Upon selecting College, the employee must select a Branch of Study. But when selecting Highschool, selecting a branch of study is not allowed.

I have a gridview in the site that shows all of the employee's educational attainment. Here is my query:

SELECT File2.file2_id,
    File2.candidate_id,
    ee.education_establishment,
    s.school,
    File2.school_others,
    File2.start_date,
    File2.end_date,
    bs1.branch_of_study AS branch_of_study_1,
    c.certificate,
    File2.course_appraisal,
    bs2.branch_of_study AS branch_of_study_2
FROM File2
    INNER JOIN Education_Establishments ee
            ON File2.education_establishment_code = ee.education_establishment_code
    INNER JOIN Schools s
            ON File2.school_code = s.school_code
    INNER JOIN Branches_of_Study bs1
            ON File2.branch_of_study_1_code = bs1.branch_of_study_code
    INNER JOIN Branches_of_Study bs2
            ON File2.branch_of_study_2_code = bs2.branch_of_study_code
    INNER JOIN Certificates c
            ON File2.certificate_code = c.certificate_code
 WHERE File2.candidate_id = 100003

The query returns only results for the College entries for employee number 100003. I assume this is caused by having null values in the Branch of Study fields for Highschool entries.

I want a query that will show all entries (College and Highschool) and at the same time showing the text value (not the code) from the reference tables.


texts: How to update a column in a table with data from another, unrelated tables columns data jus in order it selected?

texts:
There are two tables that unrelated. I want to update a field in first table with data that selected from second table in some order. There is no relations between tables, fields and data.
I have just errors from my try outs.
PS: Using MySQL.


texts: I know that's it's possible to do a remote mysqldump if I specify --host but is it possible to create the file in the remote server as well?


texts: Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

texts:
Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face.
Our data model consists of two entities, which shall be labeled as A and B. In order to keep things simple, all their attributes will be of int type.
Entity A has following attributes: D and X;
Entity B has following attributes: D and Y;
Problem
Since both entities share common attribute D, I have decided to apply type/subtype design.
I am not sure that my implementation is correct, thus I am asking here for design review.
My Implementation
-- lookup table for discriminator column
CREATE TABLE ClassType
(
  ClassTypeID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Class_Description VARCHAR(50) NOT NULL
);

-- inserting types A and B from our example
INSERT INTO ClassType (Class_Description)
VALUES ('A'), ('B');

-- creating base class table
CREATE TABLE BaseClass
(
  BaseClass_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ClassTypeID INT NOT NULL, -- FK to Type
  D int
);

ALTER TABLE BaseClass
ADD CONSTRAINT [FK_BaseClass_ClassType]
FOREIGN KEY (ClassTypeID)
REFERENCES ClassType (ClassTypeID);

-- we need this constraint in order for foreign keys in subclasses to work
ALTER TABLE BaseClass
ADD CONSTRAINT [FK_AltKey]
UNIQUE (BaseClass_ID, ClassTypeID);

-- creating subclasses:
CREATE TABLE SubclassA
(
  BaseClass_ID INT NOT NULL PRIMARY KEY,
  X int,
  ClassTypeID AS 1 PERSISTED -- calculated field, ensures integrity
);

ALTER TABLE SubclassA
ADD CONSTRAINT [FK_SubclassA_BaseClass]
FOREIGN KEY (BaseClass_ID, ClassTypeID)
REFERENCES BaseClass (BaseClass_ID, ClassTypeID);

CREATE TABLE SubclassB
(
  BaseClass_ID INT NOT NULL PRIMARY KEY,
  Y int,
  ClassTypeID AS 2 PERSISTED -- calculated field, ensures integrity
);

ALTER TABLE SubclassB
ADD CONSTRAINT [FK_SubclassB_BaseClass]
FOREIGN KEY (BaseClass_ID, ClassTypeID)
REFERENCES BaseClass (BaseClass_ID, ClassTypeID);

This is how the database diagram looks in SQL Server 2012:

Questions

Did I make any mistakes in my implementation?
Apart from making class type (in subclass tables) calculated and persisted, is there anything else I can do to prevent mistakes during INSERT/UPDATE/DELETE?


texts: Get most recent 100 records from the database

texts:
I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever Insert/Update operation perform on the table.

How do I write a query to get the most recent (based on systimestamp column) 100 records of the database in a single result set ?


texts: Difference between character_maximum_length and character_octet_length

texts:
Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here.

I wanted to get some information about my Stored Procedure parameters dynamically, so I wrote this script:

SELECT  PARAMETER_NAME,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        CHARACTER_OCTET_LENGTH
FROM    INFORMATION_SCHEMA.PARAMETERS
WHERE   SPECIFIC_NAME = @ProcName

The question is what the difference between CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH. Could you give me example of when they would have different results?

Thanks in advance.


texts: Get the Record where only Two Records exists in SQL

texts:
I want to get the parentID where user1 and user2 are using and only the two of them are using.
for example,
ParentTbl
| ID   |           Remarks              |
|------|--------------------------------|
| 1    | only one use this              |
| 2    | two user, _this is the ouput_  |
| 3    | three users use this           |

UserTbl
| ID | UserID | ParentID   |
|----|--------|------------|
| 1  |    1   |      1     |
|_2_ | ___1___|   ___2___  |
|_3_ | ___2___|   ___2___  |
| 4  |    1   |      3     |
| 5  |    2   |      3     |
| 6  |    3   |      3     |

It should be Parent 2
I already have :
select ParentID from UserTbl
where UserID IN (1,2)
group by ParentID
having COUNT(*) = 2

But the output is wrong.


texts: Planned release date for the next version of SQL Server

texts:
Has Microsoft published a roadmap for the next version, identifying a planned release date?

If not, how long do you think before the next version becomes production ready?


texts: SSIS How can I write to a Specific Cell in an Excel Sheet

texts:
I am trying to complete what I thought would be a very simple task but after hours of looking thru various articles and attempting different methods, I still have not been able to Write to a specific Cell using SSIS.

All I am trying to do is write "DOB" in cell D2 in an excel sheet.

I tried using SQL COMMAND in the Execute SQL Task componenet to do the UPDATE of the 1 cell but kept getting error messages. Below is the code I tried. SSIS came back with an error saying it was expecting at least 1 paramater...

update [Sheet1$D2:D2] SET F1='DOB'

I also tried

INSERT INTO [Sheet1$D2:D2] VALUES ('DOB')

but got the following error message:

  This table contains cells that are outside the range of cells defined in this spreadsheet.

I tried a few different C# and VB scripts but none of them did the trick.  Any ideas or suggestions?

I tried modifying the script in the below article to accomplish my task but was unsuccessful

http://bidn.com/blogs/KeithHyer/bidn-blog/2475/updating-a-single-excel-cell-using-ssis

I'm thinking there's got to be an easier way.


texts:
I tried to install MongoDB using this command:

apt-get install mongodb=3.0.1

But the version 3.0.1 was not found.

How can I install it?


texts: Can I add "Included Columns" to an index without affecting performance?

texts: I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly affect performance if I do it through the SSMS GUI?


texts:
I'm trying to pass an array of items to be used in a NOT IN operation:

ALTER FUNCTION [dbo].[fnTEST] (@CODES VARCHAR)
RETURNS TABLE
AS
RETURN
(
    SELECT
        T1.CODE AS 'CODE',
    (SELECT COUNT(*)
        FROM TABLE1 AS T2 WITH (NOLOCK)
        WHERE T2.CODE NOT IN (@CODES)
    ) AS 'COUNT'
    FROM TABLE1 T1 WITH (NOLOCK)
)

Then I call it like this:

SELECT * FROM fnTEST ('500,250,202,900,204,200,300,400,600,800')

That doesn't work. If I try to call it without the quotes, I get an error of too many parameters (which is expected).

Is there a solution for this?


texts: Is there a generic framework/design pattern/library for real time replication of MySQL database onto other servers?

texts:
Summary

I need to backup in real time updates/inserts to a MySQL database. Cron/batch jobs appear to be unsuitable for this particular requirement due to the time interval meaning that the data would only be as recent as the last cron job date/time.

Background

I have three LAMP (Linux, Apache, MySQL, PHP/Perl) servers on three locations each issuing small amounts of data regularly requested by client applications on other machines. Each server logs the data that it has issued. Each server holds configuration data that determines the kind of data issued to the clients. Both the logs and the configuration data are to be stored in a mysql database on the server.

Considered solution - servers backup each other in real time, cron jobs not suitable it seems

I am considering backup strategies for each server. Cron job based backups at certain intervals are unsuitable, owing to the necessity for the logs to be current in the backup, containing the very last entry before the server failed. For example, a cron job say every 4 hours would have data that is 4 hours old and not data since then.

So the strategy I am considering is to use the servers to backup themselves, sending their log data to each other, each log entry identifying which server it was from, as well as date stamp. This strategy seems bespoke and specific to this application because the code and data structures are specific. So I wondered:

Is there a design pattern or library or even a framework that does real time MySQL replication?

Answered questions I have already considered on Stack Overflow are:

https://stackoverflow.com/questions/7182791/daemon-software-to-update-mysql-database-in-background (similar question, but their domain - bidding needs a different solution from mine, answers here appear to be based around bidding amount, my domain isn't so much about content of the database entries, just that the latest needs to be backed up)
https://stackoverflow.com/questions/6471879/is-there-a-way-to-listen-for-a-database-event-and-update-a-page-in-real-time (more about keeping the UI up to date to reflect the database (nice question, will use it sometime) but mine is about backup)
https://stackoverflow.com/questions/5221164/need-advice-on-developing-real-time-system-using-php-mysql (nice broad question that would cover my question but theirs is again a slightly different domain concerned with technologies I am not using: OpenID, JQuery, PHP frameworks such as CodeIgnitor)
https://stackoverflow.com/questions/4200515/how-do-i-do-realtime-database-polling-in-mysql-php (for my problem, probably don't want to poll for changes as this has similar limitations to cron in that data is only as up to date as last poll)
Also looked at MySQL documentation, but could not find anything for real time replication, only batch based solutions. (Willing to stand corrected though if others can suggest to me otherwise)


texts:
So I need to create a trigger that creates a new evidence number for each case. For instance, case #1 can have evidence #'s 1,2,3,4 and so on. Case #2 can also have evidence #'s 1,2,3, etc.

So I have a "Case" table (CaseID as PK) and a "Evidence" Table (EvidenceNum as PK and CaseID as FK and associated attributes)

So everytime I search for a specific caseID, I want a new "EvidenceID" column to populate. Such as Evidence item #1, #2, and so on. So these numbers can be repeated for each case. Hence why this is not the primary key. EvidenceNum is primary key but that wont be seen by the end user. Any help with this??


texts:
I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to the User and to the Tracks table.

I'm getting the data from an API, that sends it in the correct order, latest likes sent first. But there's no field like a date when it was liked on. It's only the order. Now I want to put the likes in my database in the correct order, so I made a third column in my Likes table that is named liked_order. It's an AUTO_INCREMENTED value, therefore my foreign keys and the liked_order attribute are my primary keys for this table. Unfortunately it is now possible for a user to like a track twice. If the AUTO_INCREMENT value wouldn't have to be a primary key everything would be fine.

I've ran out of ideas...

EDIT: I have to use MySQL


texts:
I was trying to get multiple rows into a single row with dynamic columns created as per the data. I have two tables combined with foreign key.

Table 1:

| id | name | Invoice value | invoice_date |
|----|------|---------------|--------------|
| 1  | A    | 5000          | 30-01-2016   |
| 2  | B    | 8000          | 02-05-2016   |
| 3  | C    | 10000         | 03-05-2016   |

Table 2:

| id | invoice_id | duedate    | amount | percentage |
|----|------------|------------|--------|------------|
| 1  | 1          | 15-01-2016 | 2500   | 50%        |
| 2  | 1          | 30-01-2016 | 2500   | 50%        |
| 3  | 2          | 15-02-2016 | 8000   | 100%       |
| 4  | 3          | 15-05-2016 | 5000   | 50%        |
| 5  | 3          | 19-05-2016 | 2500   | 25%        |
| 6  | 3          | 25-05-2016 | 2500   | 25%        |

Desired output:

| name | invoice_value | invoice_date | due date1  | due amount1 | due date2  | due amount2 | due date3  | due amount3 |
|------|---------------|--------------|------------|-------------|------------|-------------|------------|-------------|
| A    | 5000          | 30-01-2016   | 15-01-2016 | 2500        | 30-01-2016 | 04-11-1906  | null       | null        |
| B    | 8000          | 02-05-2016   | 15-02-2016 | 8000        | null       | null        | null       | null        |
| C    | 10000         | 03-05-2016   | 15-05-2016 | 5000        | 19-05-2016 | 2500        | 19-05-2016 | 2500        |

When I tried have used group-concat for the multiple columns it's giving results with comma separated. But I want as desired output. Please somebody help to solve this issue how to write a query for this.

I was using the following query but it's giving results as comma separated result:

SELECT  T1.name,T1.invoice_value,T1.invoice_date,T1.duedate,T1.dueamount
    FROM
      ( SELECT  table1.name , table1.invoice_value, table1.invoice_date,
               group_concat(table2.duedate1) as duedate,
               group_concat(table2.dueamount1) as dueamount
            FROM  table1
            LEFT JOIN  table2  ON table1.id=table2.invoice_id
      )T1
    Group By  T1.id


texts: How to make cdc skip the point that it is stuck at?

texts:
I have an issue where the transnational log has grown to its max size.
Running fn_dblog I noticed that its complaining that a drop was done. It looks like cdc is stuck there. The description column in fn_dblog displays DROPOBJ;0X01.......

Also, the cdc capture job gives the following error message "...Msg 8152, Level 16, State 2, String or binary data would be truncated. For more information, query the sys.dm_cdc_errors dynamic management view."

So this is what happened that could have caused this...

I created a script that (1) disabled triggers (2) created table (3) created cdc table. I ran this and it was successful.
I made a mistake with the table name, so I dropped the table. I did this without disabling the cdc for this table.
I had then edited the script (mentioned in point 1) by correcting the table name and ran it again, yes it complained about the cdc already exiting.

I did not do anything else at this point.

Can someone please advise on a solution to maybe allow cdc to bypass where it is stuck at.

I'm new to databases please bare with me.


texts: PostgreSQL function that returns a 'casted' complex type from query

texts:
I tried to define a PostgreSQL function that returns a complex type from a query via:

CREATE TYPE last_contract AS (
    contract_id bigint,
    contract_date timestamp
);

and

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) RETURNS last_contract AS $func$
DECLARE ret last_contract;
BEGIN

    SELECT DISTINCT ON (tdf.delivery_id) ROW(tcf.contract_id, tcf.date)::last_contract INTO ret
        FROM task_delivery_fetch tdf
        INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id
        WHERE tdf.delivery_id = delivery
        ORDER BY tdf.delivery_id, tcf.contract_id DESC
        LIMIT 1;

    RETURN ret;

END;
$func$ LANGUAGE plpgsql;

sadly, all I get is an error:

SELECT delivery_last_contract(12197);
ERROR:  invalid input syntax for integer: "(13605,"2016-12-06 00:00:00+01")"
CONTEXT:  PL/pgSQL function delivery_last_contract(bigint) line 5 at SQL statement

Isn't there a way to actually return a single complex type returned from a query?


texts: What does "as anonymous on" means in mysql's general log?

texts:
In mysql's general log I see things like:

141017  8:57:31     4 Connect   root@localhost as anonymous on

What is this "as anonymous on" thing even means? I log in as root@localhost. Am I still anonymous, or even "anonymous on"?

UPD

$ systemctl stop mysqld
$ rm -f /var/lib/mysql/yuri.log
// enable general log in /etc/mysql/my.cnf
$ systemctl start mysqld
$ mysql -u root -p -BNe 'SELECT USER() HowYouAttemptedToLogin, CURRENT_USER() HowMySQLAllowedYouToLogin;'
Enter password:
root@localhost  root@localhost
$ mysql -u root -p -BNe 'SELECT host, user FROM mysql.user;'
Enter password:
127.0.0.1   root
::1 root
localhost   hostname
localhost   root
$ cat /var/lib/mysql/yuri.log
/usr/bin/mysqld, Version: 10.0.14-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /run/mysqld/mysqld.sock
Time                 Id Command    Argument
141204 19:03:19     3 Connect   UNKNOWN_USER@localhost as anonymous on
            3 Connect   Access denied for user 'UNKNOWN_USER'@'localhost' (using password: NO)
141204 19:04:55     4 Connect   root@localhost as anonymous on
            4 Query select @@version_comment limit 1
            4 Query SELECT USER() HowYouAttemptedToLogin, CURRENT_USER() HowMySQLAllowedYouToLogin
            4 Quit
141204 19:05:47     5 Connect   root@localhost as anonymous on
            5 Query select @@version_comment limit 1
            5 Query SELECT host, user FROM mysql.user
            5 Quit


texts: How to make use of indexes with inner join in Postgis?

texts:
I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters from tabla_a. After that calculate fall column, group them by table_a a_id column and return highest value.

I wrote a following query that meet this criteira

SELECT DISTINCT ON (a_id) *
FROM (
       SELECT
         table_b.b_id,
         table_b.height - st_3ddistance(table_b.geom, table_a.geom) fall,
         table_b.geom,
         table_a.a_id
       FROM table_a
         INNER JOIN table_b ON _st_3ddwithin(table_a.geom, table_b.geom, 50)) a
WHERE fall >= 0
ORDER BY a_id, fall DESC;

I added 3d geometry indexes:

CREATE INDEX table_a_geom ON table_a USING GIST (geom gist_geometry_ops_nd);
CREATE INDEX table_b_geom ON table_b USING GIST (geom gist_geometry_ops_nd);

However my problem is that i can't make query to use them. Query planer is keep choosing sequence scan that is slow. I run some test changing _st_3ddwithin with st_3ddwithin, <<->> < 50 , creating 50 m buffer and intersect, st_3ddistance < 50 but everytime planner is choosing sequence scan. Is there a way to use indexes with higher performance?

My query plan:

Unique  (cost=10462593.70..10473018.43 rows=1 width=144)
  ->  Sort  (cost=10462593.70..10467806.06 rows=2084945 width=144)
        Sort Key: table_a.nmbayuid, ((table_b.height - st_3ddistance(table_b.geomgr, table_a.geom))) DESC
        ->  Nested Loop  (cost=0.00..10243762.28 rows=2084945 width=144)
              Join Filter: (_st_dwithin(table_a.geom, table_b.geomgr, '50'::double precision) AND ((table_b.height - st_3ddistance(table_b.geomgr, table_a.geom)) >= '0'::double precision))
              ->  Seq Scan on e_veg_points_mv12404 table_b  (cost=0.00..1459.47 rows=47147 width=96)
              ->  Materialize  (cost=0.00..10.97 rows=398 width=56)
                    ->  Seq Scan on e_wires_mv12404 table_a  (cost=0.00..8.98 rows=398 width=56)


texts: How to optimise read/write performance a MySQL table with many rows?

texts:
I am developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 columns

id
domain_name

but I have a problem when I have imported almost 14,150,000 rows in that table. I have not done any optimisations yet (using default settings) and there is also a possibility of importing another 14,123,500 records.

Right now I am not able read that data in my application.

Which steps should I follow to perform a read,search operation faster?


texts: How to make this query use my multicolumn index?

texts:
Currently, I have a view which is defined like this:

                       View "public.customer_list"
  Column   |          Type           | Modifiers | Storage  | Description
-----------+-------------------------+-----------+----------+-------------
 id        | bigint                  |           | plain    |
 name      | character varying(100)  |           | extended |
 street    | character varying(100)  |           | extended |
 zip       | character varying(10)   |           | extended |
 city      | character varying(100)  |           | extended |
 country   | character varying(3)    |           | extended |
 phone     | character varying(100)  |           | extended |
 mail      | character varying(100)  |           | extended |
 rating    | integer                 |           | plain    |
 salesnote | character varying(1800) |           | extended |
View definition:
 SELECT c.id,
    c.name,
    a.street,
    a.zip,
    a.city,
    a.country,
    c.phone,
    c.mail,
    c.rating,
    c.salesnote
   FROM crm_customer c
     JOIN crm_address a ON a.id = c.address_id;

To better search through the List I created an index with the following:

CREATE INDEX crm_customer_big_index
    ON crm_customer (name ASC,
                     name text_pattern_ops,
                     (id::text) text_pattern_ops,
                     phone text_pattern_ops,
                     mail text_pattern_ops,
                     rating);

I search through this table with a varying where query mostly it looks like that:

SELECT *
  FROM customer_list
 WHERE lower(name::text) LIKE 'env%'
       AND rating = 3
 ORDER BY name ASC
 LIMIT 20
 OFFSET 0;

But still my analyzer won't ever use an index. Is there a way to use one?

             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=164.04..164.08 rows=15 width=129) (actual time=3.243..3.244 rows=1 loops=1)
   ->  Sort  (cost=164.04..164.08 rows=15 width=129) (actual time=3.243..3.243 rows=1 loops=1)
         Sort Key: c.name
         Sort Method: quicksort  Memory: 26kB
         ->  Nested Loop  (cost=0.28..163.75 rows=15 width=129) (actual time=0.020..3.225 rows=1 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..111.13 rows=15 width=98) (actual time=0.012..3.216 rows=1 loops=1)
                     Filter: ((rating = 3) AND (lower((name)::text) ~~ 'env%'::text))
                     Rows Removed by Filter: 2978
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=47) (actual time=0.006..0.006 rows=1 loops=1)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.580 ms
 Execution time: 3.286 ms

Currently, the query is still "somewhat" fast. However, I could have way more data inside my table. Also, crm_address has an index over street, zip, city, country, which works fine.


texts: How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.


texts: Percona-Server: slow TokuDB queries after upgrading from 5.6 to 5.7. ANALYZE TABLE doesn't resolve the problem

texts:
After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.

After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.

Typical table structure:

CREATE TABLE `letter_archiv_12375` (
 `user_id` int(12) unsigned NOT NULL DEFAULT '0',
 `letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0',
 `crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0',
 `mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `send_date` date NOT NULL,
 `code` varchar(255) NOT NULL DEFAULT '',
 KEY `crypt_id` (`crypt_id`),
 KEY `letter_id` (`letter_id`),
 KEY `user_id` (`user_id`)
) ENGINE=TokuDB

A simple query like that takes 4 seconds on a table with 200m rows.

UPDATE hoovie_1.letter_archiv_14167
                          SET unsubscribe = 1
                        WHERE letter_id = "784547"
                          AND user_id   = "2881564";

The cardinality values are correct. EXPLAIN will result in:

id  select_type table   partitions  type    possible_keys       key     key_len ref rows    filtered    Extra
1   UPDATE  letter_archiv_14167 NULL    range   letter_id,user_id   letter_id   3   const   1   100.00  Using where

The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).

The EXPLAIN will change to

id  select_type table   partitions  type    possible_keys       key     key_len ref rows    filtered    Extra
1   UPDATE  letter_archiv_14167 NULL    range   user_id,letter_id   user_id     4   const   99  100.00  Using where

We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.

So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.

Any ideas?


texts: Connection refused when adding ApplicationIntent

texts:
Environment : AlwaysOn HA group, 2 nodes, 1 database, SQL2012, Server2012R2.

Here is my .NET C# code :

string connectionString = "Server=tcp:ecouteuralwayso.xppocsql.local,1435;Database=AdventureWorks2012;Integrated Security=SSPI;";
string queryString = "select top 30 * from Person.Person;";
using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

[...]

It works perfectly. "ecouteuralwayso.xppocsql.local" accepts connections on TCP1435, I can read my database, no matter which node is the primary.

I want to use Read-Only redirection (http://msdn.microsoft.com/en-us/library/hh710054.aspx), so I configured read-only urls as described in the KB. Here are the results:

select read_only_routing_url from sys.availability_replicas

Which gives:

TCP://MSSQLCLUST-3.xppocsql.local:1433 [the "primary"]

TCP://POCSQL3.xppocsql.local:1433 [the "secondary"]

Then, I add

ApplicationIntent=ReadOnly;MultiSubnetFailover=True

To my connection string ... But it doesn't work :

  [Win32Exception (0x80004005): The remote computer refused the network connection]

  [SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.)]
     System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6564850

Without the MultiSubnetFailover, the error is :

[Win32Exception (0x80004005): No connection could be made because the target machine actively refused it]

Nothing logged in SQLServer...Any ideas?


texts: Stored procedure returns -4 after catching exception

texts:
In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming from?

Here is the stored procedure:

ALTER PROCEDURE [dbo].[pSetUserItemLike]
        @UserID int
        ,@ItemID int
AS
BEGIN
    SET NOCOUNT ON;

    -- for exception handling
    DECLARE @ErrorMessage nvarchar(max)
    DECLARE @ErrorSeverity int
    DECLARE @ErrorState int

    BEGIN TRY
        INSERT dbo.UserItemLikes
        (
            UserID
            ,ItemID
        )
        VALUES
        (
            @UserID
            ,@ItemID
        )
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627 -- IGNORE PRIMARY KEY VIOLATION
            SELECT
                @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5))
                , @ErrorSeverity = ERROR_SEVERITY()
                , @ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END

Note: I am not actually using the return value, I just noticed it as SSMS' "Execute Procedure" automatically captures the return value and puts it in a variable, and couldn't figure out why this was happening.


texts:
Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate some input optimising the statement:

delete from [dbo].[tbl1]
where exists (
    select *
    from [dbo].[tbl2] t
    where [dbo].[tbl1].[col1] = t.[col1]
    and [dbo].[tbl1].[col2] = t.[col2]
    and [dbo].[tbl1].[col3] = t.[col3]
)

The columns are as follows:

tbl1.col1 varchar(10)
tbl1.col2 datetime
tbl1.col3 varchar(60)
tbl2.col1 varchar(10)
tbl2.col2 datetime
tbl2.col3 varchar(30)

I realise that the datatype on col3 differs, I know this is bad, but would this mean the index cannot be used?

There is a non-unique clustered index on each table (not covered by this query) and a non-clustered index on both, covering all three columns included in the where clause.

tbl1 contains ~1.2 billion rows, tbl2 contains ~30 million rows. I'm expecting around 30 million rows to be deleted from tbl1.

Any help appreciated!

EDIT: FYI, tbl1 and tbl2 are on differing filegroups, but on the same disk (SAN). Also, here is the execution plan:


texts: Mysql slow query log always include "# Time:"

texts:
I am trying to parse the mysql slow log with logstash so it's important that the format is consistent. I have noticed that if statements execute at the same time, the line # Time: yymmdd is only logged once (see example below). I looked at the setting mysqld_log-long-format however this setting is deprecated and supposedily the default. I have not enabled the log-short-format option.

Is there any setting that can enforce the Time field to either always show, or always not show? I don't actually need the information since there is also a timestamp, I just need the output to be consistent.

# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;
# Time: 150419  6:00:45
# User@Host: web[web] @ localhost []
# Query_time: 3.480173  Lock_time: 0.000101 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..
# User@Host: web[web] @ localhost []
# Query_time: 3.388204  Lock_time: 0.000133 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..

The logstash config I'm using looks like this - see logstash multiline codec

input {
  file {
     path => "mysql-slow.log"
     codec => multiline {
        pattern => "^# Time"
        negate => true
        what => previous
     }
  }
}


texts:
I've the translated Sybase ASA 9 log file, and the log looks like

--UPDATE-1120-05402698411
UPDATE DBA.titulo_receber
   SET dt_pagto='2014/apr/28 11:06:08.593',
       id_situacao='BA',
       vl_pago=90,
       cd_empresa_baixa=5,
       cd_usuario_baixa=197,
       nr_recebimento=5015693,
       dt_manutencao='2014/apr/28 11:08:22.949895'
 WHERE cd_empresa=3
   AND nr_titulo=20154
   AND nr_parcela=5
   AND serie='CF05'
go
--INSERT-1120-05402698481
INSERT INTO DBA.mensagem(nr_mensagem,dt_mensagem,hora,cd_usuario_remetente,
      cd_usuario_destinatario,id_situacao,assunto,texto,id_confirmacao,
      dt_recebido,nr_mensagem_resposta,dt_mensagem_enviada,dt_ult_alteracao)
VALUES (50019297,'2014/apr/28 00:00','11:07:36.0',197,59,'N','TITULO BAIXADO','Empresa Venda: Loja3\x0D\x0AEmpresa Baixa: Loja5\x0D\x0ACliente: 3340-EZIONEI CONSTANTINO (Cliente da Loja3)\x0D\x0AT�tulo(s): 20154/05  R$ 90,00\x0D\x0ATOTAL PAGO: R$ 90,00','N',NULL,NULL,'2014/apr/28 11:06:08.593','2014/apr/28 11:08:22.952065')
go
--COMMIT-1120-05402701966
COMMIT WORK
go

I need to know the date and time in the "--UPDATE" / "--INSERT" and "--COMMIT" were executed in the db.

I can't find in the Sybase's help file nor in my search in Internet.

Kind Regards,
William Bertan


texts: Running a sql server Trace of 1 GB using sqlcmd

texts: I am load testing two sql server instances. I have a trace file from sql server 2000  database with a file size of little less than 1 GB. I am running this using the sqlcmd utility and it is been running for more than 5 hours now. The original trace file's duration was around 5 hours. Is this something expected with sqlcmd utility?


texts: Guaranteeing a FK relationship through multiple tables

texts: I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is also associated with a 'recordLabel'. When an 'album' is created, it is associated with an 'artist' and a 'manager', but both the artist and the manager need to be associated with the same recordLabel. How can I guarantee that relationship with the current table setup, or do I need to redesign the tables?


texts: I am unable to join tables in correct manner

texts:
I have created Four Tables-

1) Registration Table - it has registration id(rid),First Name of user(fname), Last Name of user(lname), gender(gender),birth date(dob).

2) Post Table - it has post id(pid), post content(pcontent), user who published post(rid as foreign key from registration table), time.

3) Plikestats Table - (where it has status of which post is liked by which user)- plikeid(unique id), post id which is liked or not(pid), user's id who have either liked the post or not(rid as foreign key), likestatus(0 for liked, 1 for unliked).

4) comment Table - (where details of all the comments will be stored.)- it has comment id(cid), post id on which comment is posted(pid as  foreign key), user's id who is commenting(rid as foreign key), comment content(ccontent), time.

I have the following query: View all of a users’ posts and display the number of likes and number of comments to that post.

I am trying to get this query by using two count functions and two group bys. I have created three separate queries, which I am listing, but I am not getting complete logic for the query.

1) SELECT post.pid
     , CONCAT(registration.fname, ' ', registration.lname) AS NAME
     , post.pcontent
FROM registration JOIN post
WHERE post.rid = registration.rid
//user’s all post

2) SELECT post.pid
        , post.pcontent
        , COUNT(plikestats.pid) AS totallikes
   FROM post, plikestats
   WHERE post.pid = plikestats.pid
        AND plikestats.likestatus=1
   GROUP BY plikestats.pid
   //number of likes to that post

3) SELECT post.pid
        , post.pcontent
        , COUNT(comment.pid) AS commentcount
   FROM post, comment
   WHERE post.pid = comment.pid
   GROUP BY comment.pid
   //number of comments to that post.

So far I have tried using Joins in many way, variables, but nothing works out. If anyone can help with it please.


texts:
In order to set up an SSAS system, I am having to install Visual Studio (SQL Server Data Tools - Business Intelligence) but when I adding this to the instance, it says it has failed the rule "Same Architecture installation", and that I have to match the new installation CPU architecture with the CPU architecture with the instance.

The instance I'm trying to link to is Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) running on Windows 7. The SSDT installation is Visual Studio 2013.

I'm not sure how I can achieve this.


texts: View the final query executed by PostgreSQL Function

texts:
I have a PostgreSQL function with Language plpgsql.
Since the EXPLAIN ANALYZE VERBOSE for my function executed like this:

EXPLAIN ANALYZE VERBOSE SELECT foo()

doesn't seem to log any valuable details except the following:

"Result  (cost=0.00..5.25 rows=1000 width=32) (actual time=122.115..122.204 rows=25 loops=1)"
"  Output: postgre.foo()"
"Planning time: 0.026 ms"
"Execution time: 122.243 ms"

I wanted to know if there is any way I can see the real query\queries used by the function, so that I can do EXPLAIN ANALYZE on these SQL queries.

Thanks.


texts: Breaking down by minutes in between two dates

texts:
In MySQL, I've got a data model with a "visit" table that has start and end datetimes.

How can I get a breakdown of the number of visits that are active per minute?

e.g.

| visit_id | start | end   |
| 1        | 10:10 | 10:15 |
| 2        | 10:15 | 10:20 |
| 3        | 10:12 | 10:18 |

Would return something similar to:

| time  | number of visits |
| 10:10 | 1                |
| 10:11 | 1                |
| 10:12 | 2                |
| 10:13 | 2                |
| 10:14 | 2                |
| 10:15 | 2                |
| 10:16 | 2                |
| 10:17 | 2                |
| 10:18 | 2                |
| 10:19 | 1                |
| 10:20 | 1                |


texts: How to execute maintenance job from script and get back the result

texts:
I am trying to find a way to execute a maintenance job from script and have it return if the the job was successful or not. I know that I can use msdb.dbo.sp_start_job to execute the maintenance job, but it will only kick it off with out returning if it was successful or not.

My ultimate goal for this is we use JAMS for all our job scheduling and we want to only need to check there for any job failures.


texts: Querying SQL Server extended properties for tables/views and its columns

texts:
I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended properties values, but when I join sys.tables and sys.columns something doesn't work properly.

Here is what I've done so far, the missing information is the value for the table's extended properties and column name.

Searching the web I wasn't able to find information about it, could somebody help me with this task?

select
    case
        when o.type_desc = 'user_table' and ep.minor_id = 0 then 'Table'
        when o.type_desc = 'user_table' and ep.minor_id > 0 then concat('Column from ', o.name)
        when o.type_desc = 'view' and ep.minor_id = 0 then 'View'
        when o.type_desc = 'view' and ep.minor_id > 0 then 'View'
        when o.type_desc = 'sql_stored_procedure' and ep.minor_id = 0 then 'Stored procedure'
        when o.type_desc = 'sql_trigger' and ep.minor_id = 0 then 'Trigger'
        else '---'
    end as object_type,
    o.name as object_name,
    ep.value as object_descripion,
    o.type,
    o.type_desc
from sys.objects as o
inner join sys.extended_properties as ep on o.object_id = ep.major_id --and ep.minor_id = 0
where len(convert(varchar, ep.value)) > 1 and o.name = 'foo'
order by o.type_desc, o.name

The desired result is something like:

object_type                 object_name         object_descripion
Table                       foo                 Table description.
Column from foo             column_1            column_1 description.
Column from foo             column_2            column_2 description.


texts: How do I shrink the MASTER log file in SQL Server 2008?

texts:
This is for a non-system database:

USE AdventureWorks
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks_Log)
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO

But should I do something different for the MASTER database?


texts: Limiting the CPU/Memory utilization for a Query in SQL server 2008R2

texts:
We have an AD-HOC process from the application team which is fetching data from one of our heavily used OLTP databases in SQLserver to migrate the data into MongoDB.

During the performance measure we can see that part of their run while fetching data from this sql server there is a spike in CPU for almost an hour, spiking to almost 60-80%.

Therefore to reduce this impact we are thinking to use Resource Governor to limit the CPU/Memory resources. I have never used RG, but reading the blogs seems it can be ideal.

Please suggest if this can be achieved or any better suggestions per your experience.

The activity above is monthly once fetching around 80 millions of data on 5 TB database in SQL server 2008R2


texts:
What is more efficient to gather table statistics:

Using dbms_stats.gather_table_stats?
Using analyze table?


texts: Oracle 11 table design for better performance for reports?

texts:
We have a requirement to design a report page.

A few factors we have are:

number of fields in report is 70
computed fields is going to around 14*8 (based on toggles)
a filter is going to have around 30 fields

Given this we have decided to come up with a physical table only for the computed fields.
Question I have is for performance purpose, is it better to fetch the 14*8 as columns given the huge data size or is it better to have this as 14 columns and 8 rows.


texts:
I have a 150 GB PROD MySQL Database (version 5.6.13).

What should be the values for below parameters:

innodb_log_buffer_size
innodb_log_file_size
innodb_buffer_pool_size (RAM - 20 GB)


texts: generating a sequence of values with differents formats and lengh of rows in PostgreSQL

texts:
I want to find documentno missing from a sequence of values from table with differents formats and length. For example https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0d3a6a647c6ef2a9063c7f3289446998
I want to find the missing documentno like : 'AGB/2021/2051','AGB/2021/2052','AGB/2021/2053','AGB/2021/2054', PROFS / 2021 / 02051_P, PROFS / 2021 / 02052_P, PROFS / 2021 / 02053_P, PROFS / 2021 / 02054_P ,'BABA/2021/02057', 'BABA/ 2021/02058', 'BABA / 2021/02059, and 'MAMAMA/2021/0206011','MAMAMA/2021/0206012','MAMAMA/2021/0206013'
CREATE TABLE c_order (
  "order_id" VARCHAR(22),
  "documentno" VARCHAR(20)
);
INSERT INTO c_order
  ("order_id", "documentno")
VALUES
  ('100001119', 'AGB/2021/2050'),
  ('100001124' ,'AGB/2021/2055'),
  ('100001126' ,'PROFS/2021/02060'),
  ('100001129' ,'PROFS/2021/02065'),
  ('100001120', 'BABA/2020/02056_p'),
  ('100001127' ,'BABA/2020/02060_p'),
  ('100001118' ,'MAMAMA/2021/0206010'),
  ('100001122' ,'MAMAMA/2021/0206014')

Query used
with cte as (
  select left(documentno,13) lpart,
         regexp_replace(split_part(documentno, '/', 3), '[^0-9]', '', 'g')::int as num
  from c_order
), minmax as (
  select lpart, min(num) minpart, max(num) maxpart
  from cte
  group by lpart
)
select lpart||t.doc_no as missing_doc_no
from minmax m
  cross join generate_series(minpart, maxpart) as t(doc_no)
  where not exists (select *
                  from c_order c
                  where regexp_replace(split_part(c.documentno, '/', 3), '[^0-9]', '', 'g')::int = t.doc_no)

This query generates a documentno but not in good format for all rows considering lengh of each row.
I want a query that work for all missing documentno.


texts: How to get wait events exact start and exact end time in MySQL 5.7 version ? Any help will be helpful to us