texts: Why does MongoDB still support MapReduce, but actively discourage people from using it?

texts:
Looking at the MongoDB documentation I see that users are discouraged from using MapReduce in favour of the Aggregation pipeline.
For example, on this page in the documentation.
But the same notice is ambiguous in terms of whether the Aggregation Framework completely replaces MapReduce or not:

Various map-reduce operations can be rewritten using aggregation pipeline operators, such as $group, $merge, etc. For map-reduce operations that require custom functionality, MongoDB provides the $accumulator and $function aggregation operators starting in version 4.4.

Does this mean that "every conceivable" MapReduce configuration can be replicated via the Aggregation Framework or not?
I understand that the MapReduce execution context might be slower than the Aggregation pipeline since the former requires a JavaScript engine which is/was/still is? slower than the Aggregation pipeline implementation.
Unlike other features, MapReduce has NOT been labeled as deprecated so far as I know. So, my question is:
Is MapReduce completely replaced (for every single possible use-case) by the Aggregation Framework? Or are there use-cases where MapReduce still provides for greater flexibility?


texts: Which local port does MySQL Workbench forward to in 'Standard TCP/IP over SSH' connections?

texts:
So if I was to set up an ssh tunnel for MySQL in terminal I would forward remote 3306 to local 3307:

ssh -L 3307:127.0.0.1:3306 user@domain.com

mysql -h 127.0.0.1 -P 3307 -u user -p db

When connecting in the same way using MySQL workbench there seems to be no option to specify the local port to forward to. So which one does it use? I'd like to be able to connect via tunnel to a remote database using MySQL workbench, and to also connect to it in the terminal via the workbench ssh connection, for which I'd need to know the local port workbench used.

Thinking it may use local 3306, I tried making a tunnelled connection in workbench and then the following in terminal, but it did not work:

mysql -h 127.0.0.1 -P 3306 -u user -p db


texts: Does MySQL have a built-in way to tell whether a view is insertable (not just updatable)? E.g., something like the IS_UPDATABLE flag, but for INSERT?

texts: The MySQL documentation for the INFORMATION_SCHEMA.VIEWS table mentions that MySQL sets a flag whenever a view is created that records whether that view is updatable, i.e., whether you can UPDATE the rows of the underlying physical tables that make up the view by querying the view. Elsewhere, in the documentation for "Updatable and Insertable Views", it mentions that there are additional rules to determine whether a view is insertable, i.e. whether you can INSERT new rows into the actual physical tables underlying the view by querying the view. I understand these rules, and I know that I could manually go through each view's definition and record whether each view is insertable myself, but what I want to know is whether there's an easy way to just pull up whether a view is insertable or not, like we can with whether a view is updatable. It would make sense that an IS_INSERTABLE flag would be visible to the user, since the IS_UPDATABLE flag is made visible to the user and MySQL distinguishes between insertability and updatability, but I couldn't find any mention of an IS_INSERTABLE flag or anything like it anywhere in the documentation.


texts: How to simply add ' and " to numeric values for height(inch) format?

texts:
I have a height field that contains two numeric values and I need to add ' and " at the end of each number to display the value as height(inch) format.
It seemed simple but couldn't find answers that I was looking for.

Height -> Height(inch)

72     ->   7'2"

65     ->   6'5"

68     ->   6'8"
.            .
.            .
.            .
.            .

Any suggestions? I am very new to sql and and would appreciate any help I can get!!


texts: The current table I am having issues with has an Publisher Range Size of 10,000, but when I look at the constraint I only see 1000 for the range size at the publisher. I have tried running sp_adjustpublisheridentityrange and changing the Publisher Range Size to 12000 to kick start it, but it still will not change.


texts:
I am trying to select the domain from the column where I have email addresses.

For example, I have a table email_table and a column email with email addresses as below.

johndoe@gmail.com
jhefid@yahoo.com
ipw@gmx.com

Expected result

gmail.com
yahoo.com
gmx.com

Here is the query I tried with CHARINDEX that works in SQL Server but not in DB2.

select CHARINDEX('@', email) from email_table


texts: How can I make this execution plan more efficient?

texts:
I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.

select cardholder_index, sum(value) as [RxCost]
into #rxCosts
from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'
 and model_set_name = 'rx_updated' and run_id in (select value from #runIds)
 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)
group by cardholder_index

https://www.brentozar.com/pastetheplan/?id=SyHl9xFeS


texts: Is there a database table design to avoid storing table names in a table?

texts:
So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in individual tables.

table : business
------------------
business_id,
(other business specific fields)

table: service_types
----------------------
service_type_id,
service_name,
(other service specific fields)

table: business_services
--------------------------
service_type_id_fk,
business_id_fk,
identifier (id to point the record in respective service table)
(other business service specific fields)

table: service_a (similarly many other services - service_b, service_c etc)
------------------
service_id,
service_type_id_fk,
(other service specific fields)

Now I need to find out

How many services a business provides
Details of each of the service.

For 1, I can simply look up business_services table.

But for 2, with the design I have, I have to store the tables names somewhere; either in server side scripting or in a dedicated table. I can may be store it in service_types table where I store the service name and its table name for further details.

Is it good practice to store table names in a table?
Or what could be the best way to address the problem?

NOTE:
None of these services are similar and they demand a table for each one. So grouping services into one table is not an option because I want it to be normalized and distribute load as well.


texts: Correct way of configuring remote users to connect to Microsoft SQL Server 2012

texts:
I have 2 Servers, a web server and a database server.
Web server runs a number of .net sites that need to connect to the database server.

Currently, I have a configuration string for each application, that specifies a database instance, user name and password, and then have to add in these users onto the Database, and give them the necessary permissions.

Locally however, I can use the integrated Ap Pool Identity. This means that when the code is deployed config strings need to be changed, and a number of db users need to be managed, along with relevent passwords.

To me this doesn't seem the best way. I have read about Managed Service Accounts, but the last thing I read implied that they are not officially supported yet (this was in 2014).

So which is the 'correct' way of connecting my app pool users from one server to the database on another?


texts:
I have a partitioned table in SQL server 2017.
I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table.

I'm using the following query :

  ALTER TABLE original_table  SWITCH PARTITION 2 TO staging_table PARTITION 2

Normally this query finishes in no time (the partition contains ~ 12M records).

the issue is when there is at the same time a query which is inserting data to original_table , knowing that the new inserted data resides in a different partition that I'm trying to switch.
In this case , when I run the alter table query , it is locked by the insert query.

How to manage this issue ?


texts: PostgreSQL selecting empty fields of integer type

texts:
I have a table and I need to select all the rows with an empty value for the fk_fc_id field (as a prelude to deleting them),

    Column     |            Type             |                         Modifiers
---------------+-----------------------------+------------------------------------------------------------
 di_timestamp  | timestamp without time zone |
 di_item_value | character varying(10)       |
 fk_fc_id      | integer                     |
 di_id         | integer                     | not null default nextval('data_item_di_id_seq1'::regclass)

However this doesn't work,

# select fk_fc_id,di_timestamp,di_item_value from data_item where fk_fc_id="";
ERROR:  zero-length delimited identifier at or near """"
LINE 1: ...di_timestamp,di_item_value from data_item where fk_fc_id="";
                                                                    ^

Trying Null doesn't work either.

If anyone has any suggestions on how to sort this, I would be very grateful.


texts:
I have found that you can do high performance updates on varchar(max),nvarchar(max),varbinary(max) columns using the .WRITE clause(example here and here). Unfortunately, there is nothing mentioned about performance optimization in the official documentation.

Could anyone tell is there any optimization at all? Has anyone made some performance test? Also if there is optimization I guess it will affect only specific situations, too.


texts:
I'm using PostgreSQL 9.2 with my web application that has three servlets with three selects.

I turn on the log.

When I send my first query: select * from "Nomi" limit 4

before it the log shows me this other line:

execute <unnamed>: SET extra_float_digits = 3

What is this command?


texts: Oracle asm disk string profile string vs param string

texts:
Oracle Database ASM disk string profile string vs param string ?

ASMCMD [+] > dsget
profile: /dev/disk*
parameter: /dev/disk*

Do we need to set both of them ?
what is consequence of setting one in these two ?

Thanks


texts: Using varchar_pattern_ops in a multi-column index in Postgres

texts:
I'm using postgres 9.5

If I have a table with 2 columns like so:

CREATE TABLE mystuff
(
  somestring character varying(256),
  timestamp_ timestamp without time zone NOT NULL
)

Will this multi-column btree index:

CREATE INDEX mystuff_idx
  ON mystuff
  USING btree
  (timestamp_ , somestring varchar_pattern_ops);

aid in performance of the following query ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like 'foo%'

what about ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like '%foobar'

For extra point please explain how a multi-column btree is used for lookup when there is a like clause in the second (or 3rd etc...) column of the index.


texts: How to specify localhost for SSIS package source server?

texts:
I have a job running on SQL Server 2012, where one of the steps is of type "SQL Server Integration Services Package". For the "Package source" I choose SSIS Catalog. For the "Server", if I type in the name of the server, everything works fine, but if I type in localhost I get an error.

How do I specify that the SSIS Catalog that I want to use is under the same server where the job is running?


texts: SQL Server 2012 Support on Windows Server 2008 R2

texts:
Is it possible to install Microsoft SQL Server 2012 on Windows Server 2008 R2 Datacenter?

On the Windows Server 2008 R2 Datacenter - I have .Net Framework 3.5.1 Features(.Net Framework 3.5.1) and I even manually installed .Net Framework 4.

  The operating system on this computer does not meet the minimum requirements for SQL Server 2012. For Windows Vista or Windows Server 2008 operating systems, Service pack 2 or later is required. For windows 7 or windows server 2008 R2, Service pack 1 or later is required. For more information, see Hardware and software requirements for installing SQL Server 2012 at: http://go.microsoft.com/fwlink/?LinkID=195092

I tried to install Microsoft SQL Server 2012 Service Pack 1 & 2?


texts:
I have an Oracle 11g and I wanted to export all my shcema objects,

with this command:

expdp as '/ as sysdba' directory=DIR dumpfile=DWH%U.dmp logfile=DWH.log filesize=40000m parallel=32 schemas=DWH

This is the start of the output I'v got:

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1840. GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 14 with process name "DW0D" prematurely terminated
ORA-31671: Worker process DW0D had an unhandled exception.
ORA-39078: unable to dequeue message for agent KUPC$A_1_100742920000000 from queue "KUPC$C_1_20190915100610"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 610
ORA-04020: deadlock detected while trying to lock object SYS.KUPC$C_1_20190915100610
ORA-06512: at "SYS.KUPW$WORKER", line 1887
ORA-06512: at line 2
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/SYNONYM/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "DWH"."T3"                            9.119 GB 21698829

The exception occured but the export hasn't failed.. and I can see that the tables start to be exported.

What is this exception mean? deadlock on object of sys?
I can see that the object "KUPC$C_1_20190915100610" is a queue of SYS..
Should I need to restart my export or can I use it after finished?

Thank you.


texts: Why would adding an index on a MySQL table slow it down significantly but ok on SQL Server and PostgreSQL

texts:
Version MySQL 5.7.2

I'm working with dummy data with int columns.

time: 1-10mm

products: 70,000 random ints with another 30,000 that are dupes from the 70,000

volume:  ranges from 500 - 1000

price:  ranges from 10 - 50 though the price stays within bounds of 1-5 diff for each product row data.

from the above, 10mm rows are created by randomly selecting a product and generating the required  row data

running a range query like...

select * from productdata where product >= 1500 and product <= 2000

takes about 4 seconds.

When I add an index on product using...

create index productindex on productdata(product)

The query now takes about 30 seconds.  Time is the only unique column in the table but setting that as the primary key does not help either.

On SQL Server and PostgreSQL I don't see the same issues with the same data and query using a non clustered index in each.  I only really have experience with writing queries for SQL Server so a bit perplexed by this.   I tried PostgreSQL as well to have another db to compare to.

All databases are the latest stable versions available.

Action Output (I had to decrease the range as the original query was taking too long)...

Without Index..

With Index..

Table status...

Buffer...

Explain Select...

Show create table...

CREATE TABLE `products` (
  `time` int(11) DEFAULT NULL,
  `product` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  KEY `productindex` (`product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Trying with time and product as key...


texts: Generate All days in moth report using PIVOT of SQL Server

texts:
My table, tblEmployeeScan(EMPLOYEE_ID varchar(7), ScannedTime)

Everyday each employee scans 2 times, 1 time or  0 time

Now I want to create a stored procedure

Create proc spMonthScanReport
    @MONTH int,
    @YEAR int

which returns a day all in month report like below :


texts: Is there any reason not to use Percona 'innodb_fast_checksum'?

texts:
I recently came across a change that Percona Server implemented over normal MySQL InnoDB. The new feature is innodb_fast_checksum

  XtraDB can use a more CPU-efficient algorithm, based on 4-byte words, which can be beneficial for some workloads (for instance write-heavy workloads on servers that can perform lots of IO).

Sounds good, but I wonder if there are any workloads in which this change would perform worse than the old method.

The documentation goes on to say:

  The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.

So, assuming that you enable innodb_fast_checksum and rebuild all your InnoDB tables as suggested to remove the old checksums, are there any situations that the extra check on the old checksums would cause performance degradation?

In short, is there any reason not to run innodb_fast_checksum?


texts: Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

texts:
I have an install of Sql Server 2008 x64, installed on two boxes. One is a Win 7 x64 workstation (Sql Server 2008 x64 SP2 developer edition), the other is a Windows Server 2003 x64 (Sql Server 2008 x64 SP2 Enterprise edition).  I am trying to create a linked server to an external vendor's Oracle instance.  I have installed the Oracle ODAC 11g 64 bit drivers using the full install (not the XCopy version).

The drivers appear to have all been installed correctly.  I have created and updated my tnsnames.ora file using the correct IP, port, etc to the remote server and rebooted.  Using the 64 ODBC admin tool, I am able to create the ODBC connection to the Oracle server and the "Test" button returns as successful using the alias in the tnsnames.ora file and the correct user ID and password.

I then go in to Sql Server 2008 and try to create my linked servers.  I can create the OLE DB linked server and it connects successfully, I can list the tables/views in the catalog and do queries against them with only one significant problem.  For tables with TIMESTAMP fields, a normal 4-part query throws fits.  Looking around this appears to be a common problem with OLEDB linked servers from Sql Server to Oracle and using OPENQUERY is the most common workaround, which I do have working.

The ODBC connection, which is what our vendor recommends using to connect to them, is where I have big problems. I can create the linked server, which appears to be successfully created using the System DSN ODBC connection I created earlier.  I can view the lists of tables/views in the catalog and they all appear to show up correctly.  However, when I try to get data, it fails completely.

If I try to right click on a view name and select Script -> SELECT To.... I get a message that says that:

  [tablename] contains no columns that can be selected or the current
  user does not have permissions on that object.

If I try to script out a SELECT manually and run it, as I know most of the column names, I get an error message:

  The OLE DB provider "MSDASQL" for linked server [linked server name]
  returned an invalid column definition for table [table name].

The vendor states that the user ID (the same one in both cases) has the proper rights to the tables/views, which it appears to as the OLE DB connection mostly works.  The Oracle server is 10g, but I don't know if it's 32 or 64 bit.  Would that make a difference?

Right now I'm working on getting this working from the Win7 x64 workstation, but a short test on the 2003 server yielded the same results.  If I have to, I guess I can make the OLEDB/OPENQUERY solution work.  However it's not ideal or recommended by our vendor.  Any ideas what I might be missing on getting the ODBC connection working?


texts: SQL Sever Transactional Replication on DB with deadlocks and blocking issues

texts: Is it feasible to try and start Transactional Replication on a SQL Server DB with deadlocks and blocking issues. Are there any other options for a close to hot backup?


texts:
I have created a sysdba user test_dba and then created database schema testdb.
In that I created 50 tables.
Now I need to create one more user "testdb1" for that schema with less rights .
That can select/update/alter data but can not create a new table.
Please help me for this.


texts: trying to create trigger, create never executes

texts:
I am trying to define a trigger in MySQL and the language to create it never executes. I have already defined and inserted data into the database with no problem.

This is the start of my trigger. As you can see I put some giberish in there to illustrate that this never executes because it produces no errors at all.

delimiter //

snthd\-
\qeiksdhsnthd-/rcg dsth
o;eui;r/cf/r
\qk-xnthr/cf

CREATE TRIGGER deleted_insert
ON item
AFTER UPDATE,INSERT
AS
BEGIN
        ...etc body of trigger

END;

delimiter ;

Executing this does absolutely nothing and does not return any output from the sql server. If I do not change the delimiter, then it does execute but produces a syntax error on the 'ON' keyword.

How can simply declare this trigger?


texts: How can I locate what's causing postgres to consume so much cpu on ubuntu

texts:
I ran top on my VM droplet and saw my postgres consuming a ridiculous amount of CPU in the background. I'm currently not running any queries.

How can I locate, given the information from the picture above, what's causing my postgres to act up. I see the PID and Command id's, wonderinf if I can use those to help me out.


texts:
I have implemented a trigger. But once I try to update the table it is giving me the error:

  can't update table 'booking' in stored function/trigger because it is already used by the statement which invoked this stored function/trigger

This is the trigger

DELIMITER //
CREATE TRIGGER CANCEL_BOOK AFTER UPDATE on BOOKING
FOR EACH ROW
BEGIN
DELETE FROM BOOKING
    WHERE NEW.F_ID=NULL AND NEW.H_ID=NULL;
END //
DELIMITER ;

please help


texts: SQL Server 2008 R2 Edition Upgrade Process

texts:
I have several SQL Server 2008 R2 Standard Edition instances that we need to upgrade to SQL Server 2008 R2 Enterprise Edition.

If I have valid SQL Server 2017 Enterprise Edition (with SA) PIDs, do I need to have SQL Server 2008 R2 Enterprise Edition media to complete the upgrade?

Please don't recommend that I upgrade to SQL Server 2017 - it is not an option at this time. It has taken years of pleading with management to upgrade to a newer version, and 2+ staff years for regression testing. It has taken an order of magnitude increase in Transaction Rate/Database size to even get the edition upgrade approved.

The question is how to perform the edition upgrade, not what the licensing implications are.

Our thought was that running SQL Server Installation Center and choosing Maintenance -> Edition Upgrade might work since the basic bits are probably the same between editions.  My guess is that the only differences are some run-time switches, configuration values, etc. so the Enterprise Edition media might not be required?


texts: How to track changes between nightly MySQL database dumps

texts:
I work with three MySQL databases (a, b & c) on a single server, that are nightly dumps of production databases. I have a fourth database (d) on that same server, that I have been given for creating new tables that connect and synthesize data from databases a, b & c.

I need to begin creating reports that reflect changes in databases a, b & c from one nightly dump to the next.

What would be the best way to capture the records from databases a, b & c, into database d, that:

example 1: records all of the unique records in tonight's dump that were not found in the last night's dump. (e.g. new account numbers in a table)

example 2: records all of the changes in value for records in tonight's dump that were different in last night's dump. (e.g. an account_status value that changes from 1 to 0).

I am new to data work and am hoping to find resources and direction on what the best practices are for such things?

Many thanks!


texts: Read only replica part of always on availability group for Reporting

texts:
As a DBA I would like to include a read only replica  into the always on availability group. XSLT / Crystal Reports will only be used.

Please see below

1) Primary Replica   (Same Data center)       Synchronous Mode Automatic Failover

2) Secondary Replica (Same Data center)       Synchronous Mode Automatic Failover

3) Secondary Replica (Read Only , Same Data center for Reporting ) Asynchronous Mode

4) Secondary Replica (Off Site , DR)         Asynchronous Mode

The will be a file share witness to form the quorum

My questions is will there be any problem if the read only replica for reporting  is included as part of the always on availability group instead of setting Transaction Replication with the Listener.

The secondary replica (Read Only) will be used for reporting only mainly XSLT and Crystal Reports , SSRS will not be used.

Any expert feedback will be highly appreciated.


texts: guessing unused junk space in table. (innodb mysql/mariadb )

texts:
We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am able to reclaim space from big table with a ALTER TABLE mydb.mytable ENGINE=InnoDB.

Hopefully that works, but I don't know table will return useful unused space a with table will not. I mean some big table have sometimes NO useless space, and some have a lot, depending how much DELETE have been done (I guess).
Is there a way to know in advance how much space is allowed for deleted record so I can run ALTER TABLE mydb.mytable ENGINE=InnoDB only on table with lot of useless space.


texts: Python version issue on Cassandra 3.11.2

texts:
Searching around I found a lot of thread about this problem (coming from 2012) but I can't find an official solution: running cqlsh.bat

  File "C:\apache-cassandra-3.11.2\bin\\cqlsh.py", line 146
    except ImportError, e:

The error suggests the script is for python 2.
In system environment I have python3, there is a Cassandra version compatible or there is an official fix for this?


texts: can I copy-paste SQL Server 2005 Express on XP Pro or Vista machine and make it work without installer?

texts:
I am having installer problems on several machines trying to install SQL Server Express 2005 Express as well as later versions (but I would be happy with 2005 anyway). However, I do have one machine where it runs fine. So, is there a way to clone the installation by copy-pasting the files and then doing some magic to make it work? So far when I put the folder into another XP Pro machine and tried launch ssmsee.exe, it didn't start - command executed in cmd quietly and nothing happened.

Is this approach in fact doable with some relevant fixes? Or is the installer the only way?


texts: Oracle 11.2.0.1.0 showing "no matching authentication protocol"

texts:
I have installed the above oracle version (personal edition) for my developmental purpose. But when I try to login through sqlplus, always it throws me the error no matching authentication protocol.

I have of course googled, and found out people asking to change SQLNET.ALLOWED_LOGON_VERSION to 8 in the sqlnet.ora file, which I changed. I even added SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT with the same versions.

There was only one entry in this file which was SQLNET.AUTHENTICATION_SERVICES = NTS. Then I added the above 3 settings. But after saving the file, I took command prompt and then sqlplus and tried to login as sys as sysdba with password and got the same error again.

Can someone please help me, as I did almost all the steps I found googling, which didn't change the result.


texts: ORA-01017 and ORA-12545 when trying to connect to oracle 12c with sqlplus

texts:
I'm trying to connect to an oracle 12c database from a remote machine, but it fails with two errors.

sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.37)(Port=1521))(CONNECT_DATA=(SID=ora12)))"

SQL*Plus: Release 9.2.0.8.0
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: user
Enter password:  (pass)
ERROR:
ORA-12545: Connect failed because target host or object does not exist

On the connecting machine I can do:

$ tnsping server12

TNS Ping Utility for Linux: Version 9.2.0.8.0
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora12)))
OK (0 msec)

So there seems to be connectivity.

If I run the same sqlplus command on the DB server, I get

$ sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.37)(Port=1521))(CONNECT_DATA=(SID=ora12)))"

SQL*Plus: Release 12.2.0.1.0

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

The TNS on the connecting machine says

server12 =
  (DESCRIPTION =
  (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SID = ora12)
  )
  )

I can also connect from SQLDeveloper using Basic Connection Type and hostname = 192.168.1.37 and port 1521 and SID=ora12.

Any ideas on how to make it connect?


texts:
The machine was restarted while I was shrinking a database and since then the SQL Server (MSSQLSERVER) not starting. After reading the blogs I replaced the master.mdf and  mastlog.ldf files from the templates folder with the same file, assuming master database was corrupted as I was shrinking this.

However the problem remains and the error log file gives the following message,

Starting up database 'msdb'.
Starting up database 'mssqlsystemresource'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf" may be incorrect.
The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
Starting up database 'model'.

I can clearly say that there is no E:\ partition on my machine. Can you please help me to fix the path which I assume is the error now?


texts: mysql query matching multiple elements from a single table

texts:
I have this awkward query I'm trying to figure out. Basically it checks a table for matching / non-matching entries (data1, data2).

My Table is:

CREATE TABLE `test`.`tab1` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `cust` INT UNSIGNED NOT NULL,
  `type` TINYINT NOT NULL, #form
  `data1` INT UNSIGNED NOT NULL, #xml
  `data2` VARCHAR(45) NOT NULL, #custXML
  PRIMARY KEY (`id`));

Test Data:

INSERT INTO `test`.`tab1`
VALUES
(1,100,1,123,'OneTwoThree'),
(2,100,1,456,'FourFiveSix'),
(3,100,1,789,'SevenEightNine'),
(4,200,1,123,'OneTwoThree'),
(5,200,1,456,'FourFiveSix'),
(6,200,1,789,'SevenEightNine');

In the given example each type has two data elements data1 and data2.

I want to get the data elements for cust=100 AND type=1 and compare them to cust=200 AND type=1

So with the above Test Data I would end up with:

Desired Result:

cust | type | data1 | data2          | match | cust | type | data1 | data2
100  | 1    | 123   | OneTwoThree    | match | 200  | 1    | 123   | OneTwoThree
100  | 1    | 456   | FourFiveSix    | match | 200  | 1    | 456   | FourFiveSix
100  | 1    | 789   | SevenEightNine | match | 200  | 1    | 789   | SevenEightNine

This I can do using a self join:

 SELECT
    a.`cust`
   ,a.`type`
   ,IF(a.`data1`=b.`data1`,a.`data1`,CONCAT("<< ",a.`data1`," >>")) AS 'data1'
   ,IF(a.`data2`=b.`data2`,a.`data2`,CONCAT("<< ",a.`data2`," >>")) AS 'data2'
   ,a.`data2`,
   IF(a.`data1`=b.`data1` AND a.`data2`=b.`data2`,'MATCH','NO MATCH') AS Matched
   ,b.`cust`
   ,b.`type`
   ,IF(b.`data1`=a.`data1`,b.`data1`,CONCAT("<< ",b.`data1`," >>")) AS 'data1'
   ,IF(b.`data2`=a.`data2`,b.`data2`,CONCAT("<< ",b.`data2`," >>")) AS 'data2'
FROM `test`.`tab1` a
LEFT JOIN `test`.`tab1` b ON (a.`type`=b.`type`)
WHERE 1=1
AND a.`cust` = 100 AND b.`cust` = 200
AND (a.`data1`=b.`data1` OR a.`data2`=b.`data2`);

If data1 OR data2 changes that's fine, as the above query handles that.

UPDATE `test`.`tab1` SET `data1` = 7890 WHERE id = 6;

cust | type | data1     | data2          | match    | cust | type | data1       | data2
100  | 1    | 123       | OneTwoThree    | match    | 200  | 1    | 123         | OneTwoThree
100  | 1    | 456       | FourFiveSix    | match    | 200  | 1    | 456         | FourFiveSix
100  | 1    | << 789 >> | SevenEightNine | no match | 200  | 1    | << 7890 >>  | SevenEightNineZero

The problem I have is if both data1 and data2 change then my comparison falls apart.
e.g.

UPDATE `test`.`tab1` SET `data1` = 7890, `data2` = 'SevenEightNineZero' WHERE id = 6;

I am trying to get a result similar to:

cust | type | data1     | data2                | match    | cust | type | data1       | data2
100  | 1    | 123       | OneTwoThree          | match    | 200  | 1    | 123         | OneTwoThree
100  | 1    | 456       | FourFiveSix          | match    | 200  | 1    | 456         | FourFiveSix
100  | 1    | << 789 >> | << SevenEightNine >> | no match | NULL | NULL | NULL        | NULL
NULL | NULL | NULL      | NULL                 | no match | 200  | 1    | << 7890 >>  | << SevenEightNineZero >>

I figure I could put it into a MySQL Stored Procedure, and use a cursor to loop through the data and do the comparison.
Or I could simply break it up and put each customers data into temporary tables, and run each comparison as a separate query to build another temp table with the result.

But if there's a way to do it in a single Query it would be far better.


texts:
I am just reading through the documentation for the SQL Server 2012 exams and I saw the following point:

  case versus isnull versus coalesce

Now, I know HOW to use each one but I don't know WHEN to use each one. Please could some body clarify. Thanks.

Ps. Please could we have a tag for exam questions?


texts: How to remove the second repetition of combination in mySQL

texts:
I have a datable like above and in that, unfortunately, repetitions occurs. How can I remove the repeated combination of 2nd, 3rd and 4th column and I need to make those three columns unique in combination.

Please help to write a query to do this, because there are 13000+ rows, so manual editing is hardly not possible.

Thanks in advance!


texts:
We have a MySQL instance for unit tests. Our unit tests work very heavily on MySQL. Therefore, we put /var/lib/mysql dir in tmpfs file system because we don't need persistent data in unit tests.

Our performance peaked when we did this; however, I would like to consult with you what's the best configuration for MySQL in this case.

It's very common to put MySQL in tmpfs (memory) and run non-important stuff like unit tests. The question is: What's best configuration for InnoDB to run in tmpfs?

These are the configs we have in my.cnf:

max_connections=350
innodb_fast_shutdown=2
innodb_log_file_size=1048576
innodb_doublewrite=0
innodb_flush_method=O_DIRECT

All our databases are in InnoDB. We don't mind losing some of the data.

P.S. You might ask at this point, why not MEMORY engine? That's because MEMORY has table-level locking only, and part of our unit tests are based on this.

EDIT:
We've finished this project, we based our configuration from this blogpost:
http://jotschi.de/2014/02/03/high-performance-mysql-testdatabase/


texts: Most efficient way of finding rows in a table that don't exist in another -- SQL Server

texts:
What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the unique key is a VARCHAR(60) field? (I know, a bad idea to have this column as unique key, but I can't change it).

Specifically:

Would using a CHAR(60) versus VARCHAR(60) going to help?
How about hashing the key to binary(20)? Do you expect a significant
improvement in join?


texts: updating associated records' tsvectors when the primary record is updated

texts:
I have the following trigger function that updates the object's tsvector column when its details change:

CREATE OR REPLACE FUNCTION customer_search_trigger() RETURNS trigger AS $$
    BEGIN
        NEW.customers_search_vector :=
            setweight(to_tsvector('english', CONCAT(NEW.first_name, ' ', NEW.last_name)), 'A') ||
            setweight(to_tsvector('english', NEW.email), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.physical_address,'')), 'D');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

Customer records are associated with Project records, via the customer_id column on the projects table. The projects table also has a tsvector column, named projects_search_vector, and that column contains tsvectors for not just the project details, but also the project's customer:

CREATE OR REPLACE FUNCTION project_search_trigger() RETURNS trigger AS $$
    DECLARE
        customer record;

    BEGIN
        SELECT first_name, last_name, email, physical_address INTO customer FROM customers_customers WHERE id = NEW.customer_id;
    NEW.projects_search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.name,'')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.description,'')), 'B') ||
        setweight(to_tsvector('english', CONCAT(customer.first_name, ' ', customer.last_name)), 'D') ||
        setweight(to_tsvector('english', customer.email), 'D') ||
        setweight(to_tsvector('english', coalesce(customer.physical_address,'')), 'D');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

This way, when the user searches for "Bob Green", any projects that belong to Bob Green (the customer) are also returned.

QUESTION: I now want to make it so that when a customer's details change, the associated projects' tsvector columns are also updated. In other words, if the customer's last name changes from "Green" to "Brown", then when the user searches for "Brown" I want their projects to still get returned in the results.

How can I do this?


texts: Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks

texts:
We've got a strange discrepancy on one of our SQL Server 2014 instances between the amount of memory being used for cached plans as per sys.dm_exec_cached_plans and the amount of memory used as per sys.dm_os_memory_clerks looking at the CACHESTORE_SQLCP type (which I understand is for adhoc query cached plans).

If we query the cached plans as below:

select cp.cacheobjtype, cp.objtype,
  sum(cast(cp.size_in_bytes as money))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype;

then we appear to have about 90 MB being used in total for cached plans, with only 2MB being used for Adhoc plans.  There are only 300 plans in the cache too.

However, if we look at the dm_os_memory_clerks view as below:

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type = 'CACHESTORE_SQLCP'

then it is reporting that roughly 12 GB is being used.  Our instance has approx. 300 GB of RAM in it.

We'd like to understand the discrepancy, and ideally take some steps to ensure that the plan cache is used effectively (i.e. has more than 300 plans in it to improve the cache hit ratio which is currently very poor).  Being able to account for this space would be the first step.

Any thoughts on what the discrepancy could be and why this space is not being used for caching plans?


texts: Loop over all records in a table and edit a column in SQL Server

texts:
How can you loop over all the rows in a SQL Server table and edit a column? You know that:

There are a lot of IDs skipped.
The column creatie must be edit whit every time one minute. So the first must be 15:00:00, the second 15:01:00, the third 15:02:00 etc.
In total are there 54 records.
I use SQL Server 2014 Express version

I have used the query below but the maximum ID is 24697... So the loop is very long.

declare @var datetime = '2015-10-19 0:0:0';
declare @counter int = 1;
declare @max int = (select max(id) from topic);

while (@counter <= @max) begin
    update Topic set Creatie = Creatie + @var where id = @counter;
    set @counter += 1;
    set @var += '0-0-0 0:1:0'
end;

Because I do every time @var += '0-0-0 0:1:0', I don't know what the result goes be after 24697 times (if it will works).

Here are the top 25 rows.

PS1: the year 2131 is a mistake my myself. don't look at it and use the year 2015.

PS2: I work with a 24h clock.


texts: How to copy table data from one db to another?

texts:
How can I do this when the table has a constraint and already exists in both databases? I need to rewrite the data in the table on my server with the data in the table on my development computer.

I have tried various forms of pg_dump, using both plain-text and binary formats. I have also tried using the --clean and --data-only options, but what always gets me when I try to restore is the constraint on the table.

pg_restore -O -d database_name dump.pg

Results in this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2409; 0 56001 TABLE DATA attendees
pg_restore: [archiver (db)] COPY failed for table "attendees": ERROR:  duplicate key value violates unique constraint "attendees_pkey"
DETAIL:  Key (id)=(237) already exists.
CONTEXT:  COPY attendees, line 1
WARNING: errors ignored on restore: 1

I'm trying to do this using just pg_dump/pg_restore with no editing of the server db between running the two commands. In other words, I don't want to have to go in and manually drop table attendees cascade before running the restore.

Is this possible? If so, how?


texts: Mysql Full Text Search with word delimiter

texts:
I have the following paragraph,

Lorem ipsum dolor sit amet, consectetur adipiscing elit,
sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Methodology

Lorem ipsum dolor sit amet, consectetur adipiscing elit,
sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Define Methodology

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do
eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim
ad minim veniam, quis nostrud exercitation ullamco laboris nisi u
t aliquip ex ea commodo consequat.

Categories of Methodology

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do
eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim
ad minim veniam.

The above paragraph is the "content" column from the "documents" table in MySql database. I want to do Fulltext search into this paragraph but I want to match the text only between "<h>" tags.

My current Fulltext search query is as follow:

SELECT *
FROM documents
WHERE MATCH(content) AGAINST('How to Define Methodology')
ORDER BY MATCH(content) AGAINST('How to Define Methodology') DESC;

How should I modify this query for search made only between <h> tags? If possible, I don't want to define "Search Index" table for these heading text.


texts: Exclusion With Many to Many Relationships

texts:
I have created a select statement that show all the topics that are assigned to a book. Now I am trying to create a Select statement that shows all the topics that are not assigned to the book. Book and topic have a many to many relationship.

Problem: I am getting the topics that are assigned to other books because technically they are not assigned to my book.

In depth look: I have a database full of books. A book can have many topics and a topic can have many books. I have create a user interface for adding a book. This interface shows all the topics available when the user choose let us say three topics three records are created in the books_has_topics table one field has an id for for the book the other for the topic. I also have a user interface for updating the book. I want the user to be able to change the topics.

Code:

this works it give me a list of all the assigned to the book based on the books id.

SELECT  *
    FROM  topic T
    INNER JOIN  book_has_topic BHT  ON T.topic_id = BHT.topic_topic_id
    WHERE  BHT.Book_book_id = [bookid];

This sort of works Not only do i get all the topics not assigned to the bookid I also get all the topics assigned to other books.

SELECT  DISTINCT topic_id, topic_name
    FROM  topic T
    LEFT JOIN  book_has_topic BHT  ON T.topic_id = BHT.topic_topic_id
    WHERE  BHT.book_book_id IS NULL
       or  BHT.book_book_id <> [bookid];


texts: Postgresql tries to connect to fe80::1, permission denied

texts:
I upgraded a couple of days ago to IPv6 on my local test machine and since then I've been experiencing some issue with connecting to my local PostgreSQL server.
I set the host to 'localhost', which worked fine using IPv4. While using IPv6, there is a 1:150 chance that the connection fails (here is a testcase for all unix systems, and here is another one for all other systems (requires psycopg2 and python3)).
As it turns out it is because the loopback interface (lo0) is configured like this:

lo0: flags=8049<UP,LOOPBACK,RUNNING,MULTICAST> mtu 16384
    options=3<RXCSUM,TXCSUM>
    inet6 ::1 prefixlen 128
    inet 127.0.0.1 netmask 0xff000000
    inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1  # line 5
    nd6 options=1<PERFORMNUD>

Line 5 is especially interesting. It says use IP address fe80::1 for connections (and which is actually used about every 150th time).
Is it safe to allow all connections (without password) from fe80::1 or did my PC just happen to be number one on the local network? Can my IP change when I'm on another network (I used to 10.0.0.1 on IPv4 but that changed occasionally depending on where I am).
If other computers can also use fe80::1, how can I prevent postgresql from connecting to my server using that address? If I change the host to "::1", will postgresql even work on networks (routers) that don't support IPv6 / haven't IPv6 enabled? Or do I need to use IPv4 (i.e. 127.0.0.1) for 100% stable connection?

Additional tags: IPv6, loopback
(Sorry I can't create tags on my own, yet).


texts:
Some background: I'm migrating a database application from MySQL to AWS' serverless offerings, Aurora and Redshift. In the past I have used stored procedures and MySQL's scheduled events to create a kind of batch job runner, that allows analytics users to connect to a session, start a job and then disconnect, leaving the job to run for as long as it takes. Coming from Oracle Databases originally, I know that there is a similar facility there, so I have spent some effort trying to find the same in Redshift (which is based on PostgreSQL 8), but it turns out not to have anything like that. (I know about PostgreSQL's background worker processes, but AWS don't allow system access to Redshift).
The reason I want to use something like that is to avoid having to maintain an online session - MySQL tends to kick remote sessions off if they seem inactive for too long, and some OLAP jobs can run for very long (my record so far is just under a month), and in many cases there is no need for producing output to a session.
This leads to a more general question: is there somewhere a list of database systems that have facilities for background jobs? I have looked around - wikipedia has a decent survey of which features different databases support, but no mention of scheduled event or similar.
What are people's experiences with other database systems - do they have something like background processes?


texts: Does installing SQL Server Database Engine and Reporting Services in Azure require two virtual machines?

texts:
We are evaluating moving a large-ish web application comprising ASP.NET, Classic ASP and SQL Server Reporting Services into Azure, and we want to know the "best practice" for installing the Database Engine and Reporting Services.

In our current environment, we have a standalone Database Engine, and a separate server that hosts Reporting Services.  One of the main reasons for this was to avoid external users making direct HTTP connections to the server housing the databases - isolation for the sake of security.

My understanding of our type of MS licensing is that we only pay for one installation of SQL Server, its components just happen to be spread over two machines - this may or may not be accurate.

If we want to have a similar configuration in Azure - will we be be required to pay 2 x the per-hour charge for SQL Server because they're considered to be two independent installations?

Alternatively, is someone willing to make a convincing case that putting the Database Engine and Reporting Services in the same virtual machine does not significantly reduce security?

I'm aware of the Windows Azure SQL Reporting, but this will not cover requirements because we are already heavily dependent on Report Models, Report Builder, Scheduled Report and custom authentication.


texts: Granting access to the Job Activity Monitor

texts:
I'm trying to grant access to the Job Activity Monitor. On my model office server I can create a new login and add it to the SQLAgentReaderRole role in msdb, and the id has access to the monitor. However in my production environment the exact same process gives me the following error:

Both are 2008 R2, but production is SP2, and MO is SP3. I'm making the assumption this doesn't matter but I could be wrong.

I checked and the associated permissions (SQLAgentReaderRole, SQLAgentUserRole, PUBLIC in msdb and master) and they appear to be the same.


texts: On the INSERT statement in H2, what does `DIRECT` mean?

texts:
I noticed on the grammar documentation for INSERT in the H2 Database Engine, the optional word DIRECT. The documentation says:

  When using DIRECT, then the results from the query are directly applied in the target table without any intermediate step.

When is data inserted not “directly applied”? What are these “intermediated steps”?

➥ What is the purpose of DIRECT?

➥ In what scenarios might one be motivated to use this feature?


texts: MySQL: what is AUTO_INCREMENT “free space”?

texts:
Quote from openark-kit. The software seems unmaintained (Google Code hosted and last commit from 2013).

  oak-show-limits: show AUTO_INCREMENT “free space”.

What is this? How-to see AUTO_INCREMENT “free space” without this tool?


texts:
I have some difficulties to make a query.

Basically, I have a 3 tables: member, build and up:

member
--+---
id|...

build
--+-----------+---
id|member (fk)|...

up
--+-----------+---------
id|member (fk)|build (fk)

How can I get the total of "up" for each member (build owner)? I only know how to get it for each build:

SELECT up.build, COUNT(*) as totalUp FROM up GROUP BY build

To be clear, I'm not asking about this query:

SELECT up.member, COUNT(*) as totalUp FROM up GROUP BY member

More details

In my application, a member can "up" (it's a kind of "like") a build. When a member up a build, there is a new entry in the table up with the id of the build liked and the id of the member who liked the build. Each member can have a build.

Globally I want to get the total of likes for each build owner.

Example:

member
--+---
id|
0 |
1 |
2 |

build
--+------+---
id|member|...
--+------+---
0 | 1
1 | 1
2 | 2

up
--+------+-----
id|member|build
--+------+-----
0 |0     |0
1 |0     |1
2 |0     |2

The result of the query should be:

memberID|totalUp
--------+-------
1       |2
0       |0
2       |1


texts: Find data size of table for several identical databases on a instance

texts:
I have several identical databases with the same schema/table structure within a instance. I need to find the size of one table that they all have. For example, I have 30 databases on a instance and all the databases have a "personal information" table. I need to find a way to query the size of "personal information" table that they all have, instead of going individually to each database.

Is there a way to do this?

Similar to SP_Spaceused for size "Data Column"


texts:
Our DBA is on medical leave, I am just a developer trying to do my part. Initially, the bean counters were going to use quickbooks. However, after seeing how much time they could save with custom development they have creeped my scope.

Rather than having a quantity of a particular type of shoe in a given size (ShoeInventory.Quantity) each individual pair of shoes needs to be accounted for and thus be given an Id, LeatherCost, ManufacturingCost, InventoryCost and TotalCost.

My question: Is there any foreseeable problem if I just move LeatherCost,  ManufacturingCost, InventoryCost and TotalCost  from the Shoe table to the ShoeInventory table?

When an order of shoes arrives from a ShoeOrder I will then just add a record to ShoeInventory for each pair ordered rather than updating a quantity?

I have no reason to think that this is a bad idea but it seems to easy to actually be the right answer.


texts: Why does a UNIQUE constraint allow only one NULL?

texts: Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of NULLs?


texts: Rotate Logs in a MongoDB Replica Set installed in Windows Server

texts:
I have a Replica Set with 3 MongoDB installed in Windows 2012 Server by using a configuration file through Windows Services.

Here is my mongod.cfg

    storage:
       dbPath: E:\MongoDB\data
       journal:
            enabled: true
systemLog:
      destination: file
      path: E:\MongoDB\log\mongod.log
      logRotate: rename
      logAppend: false
      timeStampFormat: iso8601-local

From my understanding, by setting logRotate to rename and logAppend to false this should happen automatically. For my surprise it does not!

Accordingly to MongoDB manual

  MongoDB only rotates logs in response to the logRotate command, or when the mongod or mongos process receives a SIGUSR1 signal from the operating system.

After some digging, I found this ticket opened where it says that I should use a 3rd party tool to achieve it on Windows systems.

Is it the only way to rotate the logs on regular basis? And how would work in a Replica Set scenario?


texts: postgresql: get last/newest value for each group of rows

texts:
I have the following notifications table in postgresql:
- notification_id, bigint
- group_key, character varying(24)
- associated, jsonb object
- timestamp

The table now looks like this
+------------------------------------------------------+
| notification_id | group_key | associated | timestamp |
+------------------------------------------------------+
| 1               | key1      | {123}      | ...       |
| 2               | key1      | {456}      | ...       |
| 3               | key2      | {789}      | ...       |
+------------------------------------------------------+

The following query gives the following results:
SELECT
    a.group_key,
    MAX(a.notification_id) as max,
    COUNT(a.notification_id) as total
FROM
    data.notifications a
GROUP BY
    a.group_key
ORDER BY
    MAX(a.notification_id) DESC

+-------------------------+
| group_key | max | total |
+-------------------------+
| key1      | 2   | 2     |
| key2      | 3   | 1     |
+-------------------------+

Explanation:
the notifications with ID 1 and 2 belong to the same notification group (key1). I want to fetch the max notification ID for the group (in this case 2), the number of notifications in the group (in this case also 2).
What I want now is also the content of the column 'associated' of the last notification, so in this case from notification with ID 2 (so in this case '{456}').
This column contains a jsonb object with various keys. I want to do joins from other tables with those keys, but I don't know how to get the last JSONB object for each notification group.
I tried using LAST_VALUES but I constantly get errors in pgadmin, saying that the column "associated" should be in the group by clause, but when I do this, I don't get the correct resultset returned.
SELECT
    a.group_key,
    MAX(a.notification_id) as max,
    COUNT(a.notification_id) as total,
    LAST_VALUE(a.associated) OVER (PARTITION BY a.group_key ORDER BY MAX(a.notification_id) DESC) as associated
FROM
    data.notifications a
GROUP BY
    a.group_key
ORDER BY
    MAX(a.notification_id) DESC


texts: How to allow users to run a function, but not access objects the function is accessing

texts:
I have a PL/PgSQL stored procedure that performs various operations on tables (SELECT, UPDATE, INSERT, TRUNCATE).

I want users to be able to run the stored procedure, but not themselves perform those same operations on the tables.

Is that possible?


texts: INSERT inside PL/pgSQL function gives different result

texts:
In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function argument and using the exact same query, I get an unexpected result:

CREATE TYPE my_test_type as (part1 text, part2 text);
CREATE TABLE my_test_table (id serial, test_col my_test_type);
CREATE FUNCTION my_test_table_insert (test_arg my_test_type DEFAULT NULL)
  RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO my_test_table (test_col) VALUES (test_arg);
END; $$;

-- gives null as expected
SELECT NULL::my_test_type;

-- test_col value will be null as expected
INSERT INTO my_test_table (test_col) VALUES (NULL);

-- this performs the exact same query above but in a function,
-- but test_col value shows '(,)', which is NOT expected
SELECT my_test_table_insert(NULL);

-- this is what seems to happening in the function, but I don't understand why
INSERT INTO my_test_table(test_col) VALUES (ROW(NULL, NULL)::my_test_type);

\pset null 'NULL'
SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null FROM my_test_table;
┌────┬──────────┬───────┬───────┬─────────┐
│ id │ test_col │ part1 │ part2 │ is_null │
├────┼──────────┼───────┼───────┼─────────┤
│  1 │ NULL     │ NULL  │ NULL  │ t       │
│  2 │ (,)      │ NULL  │ NULL  │ t       │
│  3 │ (,)      │ NULL  │ NULL  │ t       │
└────┴──────────┴───────┴───────┴─────────┘
(3 rows)

Even though everything tests as null, why does the result from the INSERT inside the function show differently in the table?

Edit:
The problem can be simplified:

VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type);
┌─────────┐
│ column1 │
├─────────┤
│ NULL    │
│ (,)     │
└─────────┘
(2 rows)


texts:
Given two Microsoft SQL servers:

Say ServerA has a view titled "View1":
SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3)

and a stored procedure "sp_Foo":
SELECT * FROM View1 WHERE Column1=1

When I execute EXEC sp_Foo, which query is executed on which server? Are they all executed on ServerB? All on ServerA? Or is the view query executed on ServerB and the SP query executed on ServerA?


texts: How to tell which indexes are not being used

texts:
Postgresql 9.3
Debian 7

I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they are not used at all.

Is there any usage statistics somewhere or some trick query to do that?


texts: Why does a SELECT statement dirty cache buffers in Postgres?

texts:
I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output.
From the documentation, "dirtied" indicates:

  The number of blocks dirtied indicates the number of previously
  unmodified blocks that were changed by this query; while the number of
  blocks written indicates the number of previously-dirtied blocks
  evicted from cache by this backend during query processing.

This sounds to me like the process of marking a block dirty should only happen when updating data though.  My query is a SELECT, however, and only reads data. I would imagine it would only report hits or reads. I am obviously mistaken.  What exactly is happening in this situation, though?


texts:
My colleague having DBA ROLE has created a user for me with the following role. CONNECT,DBA,RESOURCE from his own user.

Then I login and check the detail by using the below query.

select * from DBA_ROLE_PRIVS where grantee = 'MURAHMAN';

It's fine and retrieve all the above three roles, but when I query the
table dba_tab_privs.

select distinct grantee as "User_Name" from dba_tab_privs;

This does not retrieve my user.

Then I run the below query.

SELECT distinct username, account_status,privilege,owner
 FROM dba_users
   left outer join dba_tab_privs
   on (dba_users.username = dba_tab_privs.grantee)
where dba_users.account_status = 'OPEN';

I saw the privilege column against my user is null? So, could you please help to find the privilege information against each user? Also please explain why the privilege column is null against my user? All other user have the privilege information.


texts: How to make TokuDB engine on Percona delete faster

texts:
I've done some reading on the percona website, most notably MySQL Partitioning: A Flow Chart where they recommend TokuDB as a solution to faster(er) deletes over innoDB rather than partitioning.

I've taken a sample table, and have added the same 20M rows to both tables. I'm then running tests by deleting ~ 200k rows on an otherwise quiet machine (my extra laptop).

I'm not seeing any differences in speed between deleting 200k rows on InnoDB and TokuDB - they're within a few % time-wise (ie. 45 seconds vs 43 seconds).

I installed Percona 5.6 along with the TokuDB engine on Ubuntu but did not otherwise do any configuration.

Can anyone suggest what I might be missing?

The table looks a bit like this:

CREATE TABLE `testTable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `filename` varchar(200) DEFAULT NULL,
    -- 20-ish columns
    PRIMARY KEY (`id`),
    KEY `filename` (`filename`),
    CLUSTERING KEY `clstr_key` (`filename`),
    -- 3 or 4 other indexes
) ENGINE=TokuDB AUTO_INCREMENT=20000000 DEFAULT CHARSET=uft8

I've tried doing deletes in several ways to gauge results, including:

DELETE FROM testTable WHERE id BETWEEN :start AND :end;
DELETE FROM testTable WHERE filename IN ('FilenameA', 'FilenameB', 'FilenameC');
-- Where dateCol is indexed
DELETE FROM testTable where dateCol BETWEEN :start AND :end;

Results were all pretty similar for both TokuDB and InnoDB across all methods.


texts: Is there a way to prevent SQL Server resetting itself back to read-write mode?

texts:
I'm working on a desktop app which is installed along with a database, and then connects to the database using SQL Server LocalDB when a user runs the app. The workflow is:

When the app starts, it creates and starts a named, user-specific SQL LocalDB instance.
When the user chooses a database, the corresponding MDF and LDF files get attached to the instance.
The app connects to the database to run queries and get data.

A key paradigm in the app is that "a database is just a set of files on disk", much like SQLite or MS Access. So, one should be able to change the files in the database folder, start the app again, and it would work the same, but would now be getting data from the new database.
Some database files may be in a central location, so multiple users on the same computer could in theory all have the app open at the same time, pointing to the same database.
Luckily, the app does not need to write to the database and there is a "read-only" setting in SQL Server which happens to allow multiple SQL LocalDB instances to all attach and connect to the same database at the same time, as long as they don't write to it. The app can set this by running ALTER DATABASE MyDatabase SET READ_ONLY.
The problem is that by default, "auto-close" is on, meaning that when the app disconnects from the instance and reconnects, the database gets set back to read-write mode.
A further complication is that when a user logs out and back in again, the SQL LocalDB instance is stopped, and when the app starts it up again, the database still gets set back to read-write, even if auto-close is off.
Is there any way to keep the database in read-only mode and not have SQL Server "forget"?
Notes:

I doubt using a shared LocalDB instance would be feasible because of how LocalDB permissions are tied to file system permissions.
The drive to use LocalDB is strong, given that the installation and configuration needs to be as automated and hassle-free as possible.

Update: I am going to make the app set the read-only attribute on the MDF and LDF files before it tries to attach them. I think this solves the issue.


texts:
I am designing a database which will have many inserts and selects, but no updates.

Are  there any considerations to take (in order to have high query performance) while knowing no tuple updates will occur?

There is one simple table, of int (id),timestamp,real, there will be about 100 inserts per second.

Edit: timestamp is not the insertion time, the int is a random short number.
Most of the queries are selection of data between two dates (1 minute - 1 week), for a group of id (up to 10).


texts: Oracle: How do I set a context variable?

texts:
Here's a user context, and a package that sets a value in it.

create or replace context t1_ctx using t1_pkg accessed globally;

create or replace package t1_pkg
as
    procedure setx;
end t1_pkg;

create or replace package body t1_pkg
as
    procedure setx is
    begin
        dbms_session.set_context('t1_ctx','x','1');
    end;
end t1_pkg;

But, it seems that I can't get the context variable set.  Both before and after the call to setx, 'x' remains NULL.

mh> select sys_context('t1_ctx', 'x') from dual;
sys_context('t1_ctx','x')
-------------------------
NULL

mh> exec t1_pkg.setx;

mh> select sys_context('t1_ctx', 'x') from dual;
sys_context('t1_ctx','x')
-------------------------
NULL                      <---- I expect this should be '1' ???

How do I set a variable in my user context?


texts: Connection to a MySQL server fails after server restart

texts:
Until this morning I could connect via Sequel Pro to one of our networked machines that has the MySQL server on it.

Today, however, it's another story: when I am trying to connect, it times out. Even if I start a simple PHP PDO connection and run it from my machine it returns "504 gateway time out".

It's been set up for years and the details are simple:

HOST: 192.168.1.4 --
DB: ??? --
PW: ???? --
PORT: 3306 --

etc. I have not changed or edited any users.

Now the only thing that happened overnight was we had a power cut and this machine was restarted, so I am not sure if this could have caused the issue, because I have restarted the machine many times and it also has done so automatically with updates etc.

And after trying what I could find I am stuck, at what I can do next.

Your advise would be appreciated. Thanks


texts: Is this an appropriate development environment MySQL backup strategy?

texts:
I'm a web developer and I use primarily my own laptop at work. We use an SVN repository for our project codebases, and deploy our changes to an internal development server for testing.

Our system administrator has also implemented an on-site NAS backup, onto which we mirror our most up-to-date sources every few hours automatically.

Given this scenario, is it appropriate for me to simply copy the binary database files from my filesystem to the backup media, rather than to create mysql dumps for each database? The most likely restore scenario would be a complete (rather than partial) reconstruction of my local development environment, anyway.


texts: Numerous failed authentication attempts in error log

texts:
I'm having a hellacious time trying to figure out what is attempting to authenticate against a particular database server, running SQL Server 2008 R2 Standard Edition. The following is the error littered in my logs:

  Login failed for user 'Master80'. Reason: Password did not match that for the login provided.
  [CLIENT: Server IP Address]

The interesting thing is that this account is nowhere to be found in Active Directory or within the database server, not even specific databases. I've gone through every security folder and searched AD high and low, nothing, no remnants of this account.

As far as the "Server IP Address" part of the error, that changes between 3 or 4 CITRIX servers. So, the only thing we know for sure is that it coming from a specific handful of servers and maybe some application, but no one knows which application. Perusing the server and opening apps from the program menu or checking Add/Remove Programs netted nothing.

What is it that I can do to track this down? It was suggested to start a trace, but I'm not sure what to configure as tracing is kind of nebulous to me.


texts: SQL Server database : moving from 2008 to 2005 - encrypted procedure issue

texts:
I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.

Neither do I have older 2005 backup of the database.

I do not have decrypted procedures to restore manually.

Is there any way to just copy/move them over?


texts:
What is a good/healthy mysql query execution time?

To my calculations:

  The site gets 1000 unique visitors per hr

  10 page view for each unique visitor

  5 minutes for each unique visitor (I'm ignoring this. I should,
  right?)

  1000*10=10000/(60*60) = 2.7 views per second.

  I have 3 queries on every page. 2.7*3 = 8.1 queries per second

  1 second/8.1 = 0.123

So average query execution time must be less than 0.123.

Assuming visits don't increase, can we say anything less than 0.123 works for a healthy database?


texts:
I installed Postegresql and added a database which I was using as part of a local server. However, I restarted the computer and my Postgresql database seems to be gone. The Postegresql documentation says that the data should be placed in /usr/local/pgsql/data however I did not do this and this directory is empty on my system. Also, I  cannot find a postgresql config file so I am not sure where Postgreql may have been putting my databases.

Has this happened to anyone before? If so, is there a way to recover my databases? Fortunately these databases are not so old that there is irrevocable memory loss but i would prefer to recover my data if possible.

If I can't find my data I will need to restart /recreate my data although I would prefer not to.  Any suggestions would be greatly appreciated.

thanks,
zach cp


texts: Deleting old postgresql log files (Ubuntu)

texts:
In a Ubuntu machine hosting a postgresql database, I have 16G space taken up at /var/lib/postgresql/9.3/main/pg_log. Apparently, log files from many months ago are being stored as well. I manually deleted a few to get rid of my disk full error. What's a robust way to delete old postgresql logs every week? Anything I can do in postgresql.conf to automate this process?

Currently I have the following in postgresql.conf:

#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 100MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

I'm thinking I'll enable log_rotation_age and set its value to 30d. But will this auto delete older log files?


texts: Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?

texts:
Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that is supposed to tell you which tables are corrupted:

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

In fact I have two questions:
1) Where do you execute such a script? I thought the scripting shell in MySQL Workbench would to the job by saving this snippet as a Python file and then executing it - however it reports invalid syntax already in the "for ..." line.
2) According to http://dev.mysql.com/doc/refman/5.5/en/innochecksum.html innochecksum is a utility provided by MySQL/Oracle. However, I do not seem to find it in the bin or other folders of my MySQL installation. How do I obtain it?

UPDATE: As I did not trust my own MySQL installation, I downloaded the zip files for both 32 and 64 bit versions of 5.5.31 but can confirm that a innochecksum file is not included.

Thanks.


texts: How to retrieve whether database has any tables?

texts:
Knowing to use

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

to query for a list of tables, I'm currently finding no way to retrieve a list of non-system tables only.

I.e. the above query also lists me system tables like "sysdiagrams" or "dtproperties".

So my question is:

How to query a database for existance of any user tables?


texts: How far should bind variables go in Oracle?

texts:
I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.

Now, I have a quite firm understanding where to use them but I am not sure how far this should go. Now, do I have to replace all literals with bind vars?

For instance:

select 1 from t1 ; => select :one from table t;

or

select c1, c2, from t2 where id = :id and status = 2 =>
select c1, c2, from t2 where id = :id and status = :two

or

select * from t3 where c3 > :value and exists (select 1 from t4 where <some condition>) =>
select * from t3 where c3 > :value and exists (select :one from t4 where <some condition>)

Same applies to DML.

My intuition says that everything has to be a bind variable to achieve optimal performance.


texts: EMP_DEPT database. Try to SELECT records with smallest salary, NULL column record is missing

texts:
SQL Server 2017: I have the EMP_DEPT (SCOTT schema) database with a few additional records inserted. Two of them have ZERO base salary (PORTMAN, RENO) and one employee (MCQUEEN) has NULL in the salary column.
It looks like this:

Then I'm learning SQL on these records and I want to SELECT the employees with the highest and lowest salaries.
To do this I write 3 queries:
/*      Return employee record with max/min salary.    */
        SELECT * FROM EMP
        WHERE SAL = (SELECT MAX(SAL) FROM EMP);

        SELECT * FROM EMP
        WHERE SAL = (SELECT MIN(SAL) FROM EMP);

        SELECT * FROM EMP
        WHERE SAL = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);

        --ISNULL(source column, replacement_value) function transforms NULL value into
        --any other value specified in the second ISNULL attribute.

The results are as follows:

The first query results are not a surprise - I understand them.
The second query results are not a surprise - I understand them.
But in the third query I would have expected MCQUEEN returned as well, but he's not!
The question is why MCQUEEN is not returned in the third query results??
I've treated SAL column with ISNULL function to change this NULL value to ZERO.
So why isn't MCQUEEN among PORTMAN and RENO employees with zero salary??


texts: Excessive compile blocking on sp_procedure_params_90_rowset

texts:
A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]"

I have caught these statements in Profiler. They all have durations over 3 seconds. Some over 10+. The blocking activity is the same as the link from MSDN.

The calls all use 3 part naming. All specify a different proc in form they look like the following:

exec [db1].[sys].sp_procedure_params_90_rowset N'proc1', 1, NULL, NULL
exec [db2].[sys].sp_procedure_params_90_rowset N'proc2', 1, NULL, NULL
exec [db3].[sys].sp_procedure_params_90_rowset N'proc3', 1, NULL, NULL
exec [db4].[sys].sp_procedure_params_90_rowset N'proc4', 1, NULL, NULL

What can I do to reduce this level of blocking?

(edit)
I am now seeing the same thing for:

exec [db1].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db2].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db3].[sys].sp_primary_keys_rowset N'view1', N'dbo'
exec [db4].[sys].sp_primary_keys_rowset N'view1', N'dbo'

There is a systemic something going on but I don't know what else to do. the caller is VB6 via ADO. It is ADO making these calls.

An example blocked process report is below

 <blocked-process-report>
    <blocked-process>
        <process
            id="process5bc1288"
            taskpriority="0"
            logused="0"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="28887"
            ownerId="11638114050"
            transactionname="sqlsource_transform">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000">
                    <sqltext>EXEC [dbo].[spAlertDetectByPoll] ':V:^RMAlert^:Z:^&amp;N&amp;#RMAlert#&amp;S&amp;#L#&amp;UID&amp;#19#&amp;AGN&amp;#1#&amp;DFC&amp;#103#^', 1</sqltext>
                </frame>
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process
            status="suspended"
            waitresource="OBJECT: 32767:124607697:0 [COMPILE]"
            waittime="35693"
            spid="1121"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="0"
            lastbatchstarted="2013-12-16T14:45:48.960">
            <executionStack>
                <frame
                    line="1"
                    sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf>
SET NO_BROWSETABLE OFF   </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>


texts:
I've created a new collation as per the instructions mysql. In fact this tutorial was the exact use-case I needed. I've added the collation, and restarted mysql. When I try to run

ALTER TABLE `my_table` CHANGE COLUMN `my_column` `my_column` VARCHAR(32) CHARACTER SET 'latin1' COLLATE 'latin1_fulltext_ci' NOT NULL DEFAULT '' ;`

I get the error Error Code: 1273. Unknown collation: 'latin1_fulltext_ci'. When I run SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = 'latin1_fulltext_ci'; I get:

+--------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+----+------------+-------------+---------+
| latin1_fulltext_ci | latin1             | 62 |            |             |       0 |
+--------------------+--------------------+----+------------+-------------+---------+

So, I know MySQL is finding it. I've run repair, and check for upgrade on the tables and tried creating new tables, but I keep getting the same error Error Code: 1273. Unknown collation: 'latin1_fulltext_ci'.

Does anyone know what could be causing this?

Version details

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.73-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)


texts:
Consider the following setting:

A production DB
A dev db, on which schema changes are made to enable new features

When the development of a new feature is complete, I have to manually update the prod db schema until pg_dump --schema-only on both DBs is identical. This process is error prone and tedious.

So, I am looking for a tool that can:

Show a summary of the differences between two schemas (like diff). Note that I am not looking for a mere textual diff of the schema, but a more elaborate tool that can draw conclusions like "Table X has a new column Y".
Auto-generate the SQL code that would convert one schema to another (like patch)

Is there a schema diff/patch tool that can help me convert prod schemas to the more advanced dev schemas?


texts: What is SQL Server "Denali"? What's new?

texts:
I found a new title called SQL Server "Denali" in the drop down list on MSDN, but I didn't find much information about it:

What's New (this documentation is for preview only as I see)
Top 9 New Features of SQL Server "Denali"

Does anyone have more detailed information about new features or significant bug fixes in this release? I'm hoping someone has used or tested it.

New Features

Sequences
Extended FILESTREAM (thanks to Eric Humphrey)
OFFSET & FETCH (ORDER BY Clause)
Memory Manager Changes
LAG(..) and LEAD(..) .. OVER (Partition By..Order By) clause (thanks to gbn)
Aaron's list


texts: requested WAL segment ***** has already been removed

texts:
Yesterday, I tried to run 'vacuum freeze' a table on Master instance. After that, I keep getting this error message "requested WAL segment ****** has already been removed" in the postgres log.

I checked and this segment is not in pg_xlog on Master, but I have archived this in pg_log_archive directory. My wal_keep_segments is currently set at 300.

On Slave, this segment was missing in pg_xlog. So, I copied it from the Master.  But the error message still persists in the Master's postgres log.

However, the replication between Master and Slave seems to work fine. I am using repmgr 2.0 (PostgreSQL 9.3.5) .

Any idea? Will I be able to restart the Master? Please help.

Thanks


texts: Updating multiple rows with different values in one query

texts:
I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

For instance, two updates into 1 query:

UPDATE mytable SET fruit='orange', drink='water', food='pizza' WHERE id=1;

UPDATE mytable SET fruit='strawberry', drink='wine', food='fish' WHERE id=2;

I don't understand what the CASE WHEN .. THEN ... END works and how to use it.

Wonder if someone could help me on this.


texts: How do I create a partition using a bit field in SQL Server 2014

texts:
I have a table where data is archived. I need to keep this data as it is rarely viewed.

Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When I use the designer to partition, the value look all wrong.

The table currently has 42387343 rows (29518148 archived,12869195 active), but this will rapidly increase.

I'm using a Clustered ColumnStore Index

Can / Should / How do I partition on a bit field?

I used this script, but it made the queries much slower:

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [Archived](bit) AS RANGE LEFT FOR VALUES (N'0', N'1')

CREATE PARTITION SCHEME [Archived] AS PARTITION [Archived] TO ([PRIMARY], [PRIMARY],     [PRIMARY])

DROP INDEX [IX_AttributeDataLog] ON [dbo].[AttributeDataLog] WITH ( ONLINE = OFF )

CREATE CLUSTERED INDEX [ClusteredIndex_on_Archived_635544995752812167] ON [dbo].[AttributeDataLog]
(
[Archivable]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [Archived]([Archivable])

DROP INDEX [ClusteredIndex_on_Archived_635544995752812167] ON [dbo].[AttributeDataLog]

CREATE CLUSTERED COLUMNSTORE INDEX [IX_AttributeDataLog] ON [dbo].[AttributeDataLog]
 WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [Archived]([Archivable])

COMMIT TRANSACTION

Every query will include the IsArchived field (except the queries that run to set IsArchived, and some rarely used historical reports). I would normally put it in its own table, but there are changes that can set archived data, to non archived. These changes can happen at any time.


texts:
I have a Fact table CardTransactionFact

Table Structure

TABLE [dbo].[CardTransactionFact]
    [CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionTerminalID] [int] NOT NULL,
    [SourceAccountTypeID] [int] NULL,
    [DestinationAccountTypeID] [int] NULL,
    [RimNo] [varchar](15) NULL,
    [CaptureCodeID] [int] NOT NULL,
    [RoutingCodeID] [int] NOT NULL,
    [ProcessingCodeID] [int] NOT NULL,
    [ActionCodeID] [int] NOT NULL,
    [NetworkCodeID] [int] NOT NULL,
    [ProductCodeID] [int] NOT NULL,
    [AcquiringCountryCodeID] [int] NOT NULL,
    [IssuingCountryCodeID] [int] NOT NULL,
    [TransactionCurrencyCodeID] [int] NOT NULL,
    [AmountBD] [decimal](18, 3) NOT NULL,
    [LocalCurrencyCodeID] [int] NOT NULL,
    [CardIssuerBank] [int] NOT NULL,
    [CardTypeID] [int] NOT NULL,
    [SuspectTransactionFlag] [char](1) NOT NULL,
    [ReversalTransactionFlag] [char](1) NOT NULL,
    [LocalTransactionDateKey] [int] NOT NULL,
    [LocalTransactionHourKey] [int] NOT NULL,
    [BBKRole] [char](1) NOT NULL,
    [AmountRangeKey] [int] NULL,
    [CustomerKey] [int] NULL

Size: 11GB
No of Rows : 56,959,828

It has become very difficult to access this table now, a simple Select count(*) from CardTransactionFact takes hours to execute.

most of the columns in the table are just integers that's why i didn't do any indexing.

What do you think i should do to improve this table, and increase the speed of query's to this table

If indexing which columns should i index and why
Is it good idea to Partition the table
Any Other suggestions


texts: What does postgresql do when two transactions with different isolation levels run simultaneously?

texts: According to this, postgresql supports different isolation levels per connection and even different ones in the same connection. Let's say one connection is using serializable and another is using read-uncommitted. So what happens if both connections try to execute some transaction simultaneously? Does postgresql behave as if both connections were set to serializable?


texts:
I receive a series of JSONAPI (https://jsonapi.org/format/) documents as a response to an API call that I make in my application. I need to save the API responses to my database so I can reference it again in the future (I cannot make another API call that can guarantee that I get the same data again). In the JSON documents, there will be a series of unique ids that I can use as part of a LIKE query.

Is it a good idea to save a JSON string in a column of a table? One benefit of it is that I can save the entire JSON in a single row/column. I'm concerned about scaling -- and how LIKE once I start having a lot of rows/columns of JSON documents that I need to search.

Another option is to loop through each element in the JSONAPI's data element and save all of the data in their own columns and relationship tables. This feels like a more structured way to save the data, but I'm concerned it's overkill since I really only need to reference the JSON document on occasion and can use a LIKE.

Is there a best practice for this?


texts: How crash recovery process works in SQL Server?

texts:
I would like to ask a question regarding recovery process while restoring database operation. Let's say I restored a full backup with NORECOVERY followed by restoring two t-log backups with NORECOVERY. Lastly, I am going to restore the last 3rd t-log backup with RECOVERY keyword.
The question is: when I restore the last t-log backup with RECOVERY keyword, does SQL Server perform redo as well as undo phases or just does undo phase?
It seems to me that when we restore backup with NORECOVERY keyword, SQL Server also performs redo phase. The same thing with restoring t-log backups with standby keyword. In this case, SQL Server creates a separate file with the name that contains only "undo" not any "redo".


texts: Do I need to convert varchar(32) and varcha(64) to update from MySQL 5.7 to 8.0

texts:
I am upgrading a database from 5.7 to 8.0 in MySQL and ran across this:
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5
I understand converting the schema and tables to utf8mb4 and utf8mb4_unicode_ci but regarding the changing of columns to varchar(191) I was lost.  I tried to follow their disclaimer and read the notes but I cannot see if this is actually needed to upgrade...or why it is even listed as a step.
Also, are there any issues that could arise from converting the tables to utf8mb4 from utf8 and just skipping converting the columns to a different type?


texts: Should I install SQL Server 2012 SP3 when I'm already running SQL Server 2014?

texts:
We've been running SQL Server 2014 for almost a year now, but SQL Server 2012 SP3 continues to appear in the Windows Update list (as an optional install). It seemed ridiculous to install a service pack from a previous version, so I've just ignored it.

However, we've recently started encountering an assertion failure (see below for more info), and one thing we're doing to try and resolve it is making sure the server is fully patched. We installed the latest cumulative update for 2014, but it hasn't solved the problem or made that SP3 item disappear from Windows Update. SP3 still wasn't on my radar, but then I found a Microsoft Product Support Services blog post that said to use the Best Practices Security Analyzer to try and troubleshoot assertion failures, and when I did it raised a warning that we hadn't applied that SP3.

So now I am considering applying that install, but I'm worried that I'm going to accidentally downgrade my server - not that we'd go back 2012 entirely, but that some of the cumulative updates might get reverted, or some other bizarre behvaior.

(This is not the exact assertion failure we've been receiving, but it's close. I cannot find a KB article that seems to describe the actual issue we're having - the KB linked in the previous sentence has to do with TOP N and ours has to do with a procedure using OFFSET X ROWS FETCH NEXT Y ROWS ONLY. The actual assertion failure is SQL Server Assertion: File: <op_ppqte.cpp>, line=12258)


texts: Does binary logging apply to ColumnStore?

texts:
When binary logging is enabled, does MariaDB's binary logging also record ColumnStore events, and not just InnoDB events?  For example, does it log an UPDATE made to a ColumnStore table row?
I've searched the MariaDB documentation and it doesn't mention whether binary logging applies only to some, but not all, storage engines.  Is binary logging independent of the storage engine used?


texts: Map-Reduce on `system.profile` collection

texts:
I have a simple collection posts with a few documents in it. I have set the profiling level to 2 allowing for profiling information to be collected into another collection system.profile. My goal is to use a mapReduce operation to collect information from the command entry grouped via various actors using this simple query:
db.system.profile.mapReduce(
    function()
    {
        emit( this.appName, this.command  );
    },
    function( key, values )
    {
        return JSON.stringify(values);
    },
    {
        query: {},
        out  : "command_maps"
    }
)

However, trying this in the REPL yields the following error
2021-05-10T22:09:33.494+0800 E QUERY    [js] Error: map reduce failed:{
    "ok" : 0,
    "errmsg" : "rename failed: { ok: 0.0, errmsg: \"error with source namespace: cannot write to 'test.tmp.mr.system.profile_22'\", code: 20, codeName: \"IllegalOperation\" }",
    "code" : 10076,
    "codeName" : "Location10076"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.mapReduce@src/mongo/shell/collection.js:1145:1
@(shell):1:1

I suspect mongo is trying to temporarily write into the system.profile collection and fails due to it being readonly in a sense. Is there any way to execute this successfully ?
Unfortunately the documents are too large to add here but basically its sort of like this:
DOCUMENT
{
   "appName": "actor1",
   "command": {"find": {},"ns" : "test.post""}
},
{  "appName": "actor2",
   "command": { "find": {},"ns" : "test.comments"}
}
{
   "appName": "actor1",
   "command": { "insert": {},}
}

OUTPUT
{  appName: "actor1": command_maps: '[{command: "find"}, {command:"query"} ...]' }
{    appName: "actor2": command_maps: '[{command: "find"}...]' }

Version: MongoDB shell version v4.4.5
PS     : mapReduce is working properly on regular collections


texts: Restoring MySQL database from small ibdata1

texts:
I've got a database from a dead Rasperry Pi, which I want to restore. As it doesn't boot anymore because of a damaged SD card, I only could recover the data files, but not do a normal dump. The server which ran before on the Pi is now supposed to run on a Windows PC here. Therefore I installed XAMPP and tried to use the old data files, but with

    ...
    innodb_data_file_path = ibdata1:10M:autoextend
    ...

I get only

    [ERROR] InnoDB: auto-extending data file D:\xampp\mysql\data\ibdata1
    is of a different size 512 pages (rounded down to MB) than specified
    in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

The size of the ibdata1 file is only 8MB, so I tried setting the size to this, but then MySQL complains, that the tablespace should be at least 10MB.

Is there any way to recover that data? E.g. extending the ibdata1 file manually?


texts: statement to find at least a year with register users > 1

texts:
i need to find find the years for which have at least 2 users register.

the table is

CREATE TABLE IF NOT EXISTS `rotef`.`users` (
  `id` INT(11) NULL,
  `name` VARCHAR(100) NULL,
  `year` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

and the data

INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (1,"john", 2016);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (2,"jim", 2016);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (3,"eric", 2014);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (4,"jesi", 2012);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (5,"jef", 2011);

the statement based on the above data must return 2016