texts:
i need to move the data to a different folder and drive on my cent os db server.

i added new hard drive (since we ran low on space),

i have a 1tb of hard drive and i have a new 2tb i want to put the data on on the same server (dedicated server hosted in a datacenter)

i would like to know what is the easy way to migrate the data without losing any data?

currently i have the data located in: /var/lib/mysql/ on cent os. mysql version is 5.1


texts:
Consider the following:

declare @dt datetime, @dt2 datetime2, @d date
set @dt  = '2013-01-01'
set @dt2 = '2013-01-01'
set @d   = '2013-01-01'

select convert(varbinary, @dt) as dt,
       convert(varbinary, @dt2) as dt2,
       convert(varbinary, @d) as d

Output:

dt                    dt2                     d
------------------    --------------------    --------
0x0000A13900000000    0x07000000000094360B    0x94360B

Now, I already understand from the documentation that datetime has a smaller range, and starts from 1753-01-01, while datetime2 and date use 0001-01-01 as their start date.

What I don't understand though, is that datetime appears to be little-endian while datetime2 and date are big-endian.  If that's the case, how can they even be properly sortable?

Consider if I want to know how many integer days are represented by a date type.  You would think you could do this:

declare @d date
set @d = '0001-01-31'
select cast(convert(varbinary, @d) as int)

But due to the endianness, you get 1966080 days!

To get the correct result of 30 days, you have to reverse it:

select cast(convert(varbinary,reverse(convert(varbinary, @d))) as int)

Or, of course you can do this:

select datediff(d,'0001-01-01', @d)

But that means internally somewhere it is reversing the bytes anyway.

So why did they switch endianness?

I only care because I'm working on a custom UDT in SQLCLR and the binary order of the bytes does seem to matter there, but these built-in types seem much more flexible.  Does SQL Server have something internal where each type gets to provide it's own sorting algorithm?  And if so, is there a way I can tap into that for my custom UDT?

See also, a related (but different) question on StackOverflow.


texts: Selecting consecutive rows with same values and one that differs

texts:
So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this:

userID        status     Duration_in_this_status (min)
------  -------------------   ------
Brown         idle             40
Brown         idle             ?

The ? inidcated that it is the time from this

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle
Brown   2017-10-18 03:20:28   idle
Brown   2017-10-18 03:25:28   idle
Brown   2017-10-18 04:00:28   active
Brown   2017-10-18 04:10:28   active
-> Brown   2017-10-18 04:35:28   idle
Brown   2017-10-18 04:45:28   idle

till now.

I tried to modify the answer from the above question but all I could managae was create a variable @next_time and increase it when the if goes to @groupNumber +1 but I couldn't work it out because all it does it increases the year by one.

   SELECT userID, status, TIMESTAMPDIFF(minute, MIN(tStamp), MAX(tStamp)) AS
duration
    FROM (
       SELECT
       t.*
        , @groupNumber := IF(@prev_userID != userID OR @prev_status !=
status, @groupNumber + 1 and @next_time=@next_time+1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_status := status
    , @next_time   := tStamp
    FROM t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_status := NULL) var_init_subquery
    ORDER BY userID, tStamp
) sq
GROUP BY gn, userID, statu

So can any one help me to get that next value using these user-defined variables? Thanks

MySQL version 8.0.16


texts: Does ' /*+parallel(,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

texts:
I have three tables with below structure:
create table customer_info
(customer_num        number,
 customer_firstName  varchar2(100),
 customer_lastName   varchar2(100),
 branch_code         varchar2(100));

create table branch_info
(branch_code   varchar2(100),
 branch_name   varchar2(100));

create table customer_transaction
(transaction_date  date,
 customer_num      number,
 branch_code       varchar2(100),
 transaction_count number
  )

Among these tables the one which has the biggest amount of data is customer_transaction with nearly 10 million records right now. The table has partition on column transaction_date .I want to know:
1)whether there is a difference between these two queries in terms of performance.
2)In what way these two queries are different from each other.
The point is that in the first query I'm using this /*+parallel(4)*/ and in the second query I'm referencing the table which has the biggest amount of data /*+parallel(t2,4)*/.I must say that both queries produce different execution plans.
First Query:
Select /*+parallel(4)*/
       t2.transaction_date,
       t1.customer_num,
       t1.customer_firstname,
       t1.customer_lastname,
       t3.branch_name,
       t2.transaction_count
  from customer_info t1
  left join customer_transaction t2
    on t1.customer_num = t2.customer_num
  left join branch_info t3
    on t2.branch_code = t3.branch_code;

Second Query:
Select /*+parallel(t2,4)*/ -----> t2 is "customer_transaction"
       t2.transaction_date,
       t1.customer_num,
       t1.customer_firstname,
       t1.customer_lastname,
       t3.branch_name,
       t2.transaction_count
  from customer_info t1
  left join customer_transaction t2
    on t1.customer_num = t2.customer_num
  left join branch_info t3
    on t2.branch_code = t3.branch_code;

Thanks in advance.


texts: Unable to import mysql.connector in a Python venv session

texts:
I've used venv to create a virtual environment for Python 3.5.2. I have installed the mysql.connector. The connector works fine as long as I am not in an activated venv environment.

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

With these statements I get the following:

Traceback (most recent call last):
  File "fetchone.py", line 1, in <module>
    from mysql.connector import MySQLConnection, Error
ImportError: No module named 'mysql.connector'

I have searched for a solution but most are dated several years ago. Like I said, the mysql.connector is located and works fine outside of the venv environment


texts: mysql > why replace command is not working on strings coming from json encoded?

texts:
Say in my wordpress db I've got a post_content column containing strings like this:

href=\"http:\/\/dev.example.com\/example\/wp-content\/uploads\/2019\/07\/my.pdf\"

The above is a portion of string (placed inside a link placed inside a text) that comes from data saved by a plugin (acf) which uses json as data format.

Then I run

UPDATE wp_posts SET post_content = REPLACE(post_content,'http:\/\/dev.example.com\/example\/', 'https:\/\/www.example.com\/');

Well, that won't work. Zero rows affected and no errors. Why? What am I doing wrong?

That kind of code comes from acf plugin gutenberg block in wordpress, and that's because it's json encoded. But as far as I know, shoudl be trated just as any other string.. What's the matter?

To be more precise, here below I'm attaching an image from sequelpro and from cli to prove the backslashes are there (see comments).


texts:
I am taking a course on data warehousing. Currently, I am reading through Ralph Kimball's Data Warehouse Toolkit.

I understand that a degenerate dimension is a dimension key that is stored in a fact table. It does not join to a corresponding dimension table because all of its attributes have already been placed in other analytical dimensions, thus eliminating the need to join to another dimension table.

What I do not understand is when I should make something into a Degenerate Dimension as opposed to having it as a "normal" dimension and linking it to the fact table via a Foreign Key?


texts: PostgreSQL - Simple deletes takes more time

texts:
Today I found a weird(may not be) thing in PostgreSQL 12. Im trying to delete rows from a table with where condition from another. Both are small tables. But the delete is going more than an hour but not completed.
Row count:
convo=> select count(*) from convert  ;
 count
--------
 221368
(1 row)

convo=> select count(*) from gredd  ;
 count
--------
 763150
(1 row)

Index
convert  => feed_id - indexed
gredd  => feed_id - primary key

Delete query
 delete from convert   where feed_id not in(select feed_id from gredd );

Explain
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Delete on convert  (cost=0.00..15163945284.00 rows=110684 width=6)
   ->  Seq Scan on convert  (cost=0.00..15163945284.00 rows=110684 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..135098.55 rows=761370 width=6)
                 ->  Seq Scan on gredd  (cost=0.00..128316.70 rows=761370 width=6)


texts: How can we achive the replica of databases for SQL Server?

texts:
I have been working in VB6 to VB.NET migration project.In VB6 project they have individual databases (access db .mdb) for every user now we made that to centralized database using SQL Server.

In the old application we have a MasterDB.mdb which is in sync with the local.mdb. Some times we need to sync with both the users of local.mdb. After a particular interval of time we need to sync with the MasterDB.mdb.

We need to do this in SQL Server. So, I have a done more analysis related to SQL Server. I read a MSDN forums and many articles related to replication in SQL Server (Related Scenario). I think that merge replication matches to my requirements.

Can anyone suggest whether I have chosen the correct replication? Do we have any environment changes to set up this replication?


texts:
I have installed Oracle 10g Lite on an administrative system with 32-bit Windows XP as the OS. I have N client systems with a mix of 32/64-bit Windows 7/8 OSes.

How can I configure the server as well as the clients so that the database can be accessed by all? With it we have a wireless router, IB-WRX300-N , providing the internet to all.


texts: SQL Server Service Broker Compatability across versions?

texts: I'm planning a project to upgrade internally-developed app from SQL Server 2005 to SQL Server 2012. I hope to use Service Broker as a bridge while in transition. Are there any issues to consider in a forward-compatibility scenario like this other than newer features like multicasting not being present? I can't find anything in BOL specifically about compatibility.


texts: MongoDB script to backup replication set locally to a ubuntu 14.04 server

texts:
I would like to make a Daily Backup of my MongoDB from a replication set running from ubuntu 14.04 server.

End goal would be to get a daily backup and write the backup to a remote or local share - ubuntu.

Can I batch the mongodump command?

Please help me on this


texts: MySQL partitioning table by date and hour

texts:
I have a table with a timestamp field. This table will have millions of rows every day so the idea is to create partitions by date and hour. Every day I will delete partitions older than 10 days and create the partitions for next day.
   Can you someone tell me how to define partitions for this case? I could not figure it out.
   Thanks in advance.


texts: In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from CST to GMT time? It could be helpful if I get some sample queries for retrieve the same.


texts: Instead of Trigger Continue with operation after custom logic?

texts:
I do not think what I am trying to do is possible, but I could not find any documentation on this. I have an INSTEAD OF UPDATE, INSERT, DELETE that always inserts data into a history table before performing the DML operation. So I created triggers that performed my insert into the history table and then I created 3 IF statements to figure out if the original operation was an INSERT UPDATE or DELETE operation to continue the original operation.

Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? See my code  below. We are using the standard edition of SQL Server, so no CDC to help with logging changes. Thank you.

    SET NOCOUNT ON;

    -- ========================================================
    -- STEP 1: ARCHIVE. Get the records that are being inserted
    -- or deleted and put them in the history table
    -- ========================================================

    -- INSERT INTO HISTORY TABLE HERE

    -- ========================================================
    -- STEP: CONTINUE. After archiving, perform the requested
    -- manipulation
    -- ========================================================

    IF(EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform insert into production table
    END

    IF(EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform update on production table
    END

    IF(NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
      -- Perform delete on production table
END


texts: How can I visualize the entire delete cascade of an existing database?

texts:
I have a PostgreSQL 9.6 database with 68 tables. Many of them have foreign key constraints with ON DELETE CASCADE clauses.

I would like to see the complete graph of the entire cascade. Is there a tool for that?


texts: How to aggregate the number of indexes on a table and columns per index

texts:
I have a simple query that returns every table, index and the columns of the index in a database. I'd like to add two aggregate columns that return the number of indexes on a table and then the amount of columns on each index.

I'm looking to build a report of every database on a server, each table in there and each index and column to look for tables with too many indexes and large indexes with more than 2-3 key columns.

 SELECT
 @@SERVERNAME as server_name
,DB_NAME() as database_name
 ,TableName = t.name
 ,IndexName = ind.name
 ,ColumnName = col.name
 ,ic.key_ordinal
 ,ind.is_disabled
 ,IndexId = ind.index_id
 ,ColumnId = ic.index_column_id
 FROM
 sys.indexes ind
 INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and
 ind.index_id = ic.index_id
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and
  ic.column_id = col.column_id
  INNER JOIN sys.tables t ON ind.object_id = t.object_id
  WHERE t.is_ms_shipped = 0
  ORDER BY database_name, t.name, ind.name, ind.index_id


texts:
Our oracle database dead from time to time.When i look into the log in the alert.It shows:

  Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xFFFFFFFEAA7DD0E0] [PC:0x8213123, kslgetl()+111] [flags: 0x0, count: 1]

  ORA-07445: [kslgetl()+111] [SIGSEGV] [ADDR:0xFFFFFFFEAA7C58C0] [PC:0x8213123] [Address not mapped to object] []

  DDE: Problem Key 'ORA 7445 [kslgetl()+111]' was completely flood controlled (0x6)

  Further messages for this problem key will be suppressed for up to 10 minutes

  Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/SDH/trace/SDH_p104_2581.trc  (incident=82376):

  ORA-00600: internal error code, arguments: [ksl_invalid_latch], [kslges], [0x2AFF2F2D8], [], [], [], [], [], [], [], [], []

Database version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Any idea? thanks!


texts: Copying MySQL table to another table with no missing new changes

texts:
I would like to recreate/copy a MySQL table (a actual new ibd file) - and most of the suggestions recommends the following :

CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

The suggestion works well - but from my understanding (correct me if I'm wrong), it does not insert/update records in the new table during the period of copying/recreating.

Example : During the copying, if record #45 is already inserted into the new table, and there was an update on record #45 on the original table - it won't be replicated over

Is there another way to ensure that after the full copying of data over, the data on the new table will be in the most updated state? I'm not sure using TRIGGERS is the solution for this.


texts:
I'm using the db2 driver for CLI and ODBC to configure an ODBC connection to DB2 on IBM i/OS from CentOS. I'm trying the following command:
db2cli execsql -connstring "protocol=TCPIP;database=MyDB;hostname=MyHost;ServiceName=446;uid=MyUser;pwd=MyPass"

And getting the following error which seems pretty straightforward:
[IBM][CLI Driver] SQL1598N  An attempt to connect to the database server failed because of a licensing problem.  SQLSTATE=42968

I believe I have the paths and everything correct:
# Environment
export PATH=$PATH:/opt/DB2/odbc_cli/clidriver/bin/
export DB2_CLI_DRIVER_INSTALL_PATH=/opt/DB2/odbc_cli/clidriver
export LD_LIBRARY_PATH=/opt/DB2/odbc_cli/clidriver/lib

license file: /opt/DB2/odbc_cli/clidriver/license/db2consv_is.lic

I have also attempted to move the license to a few other paths without any success. It is valid and works on another (windows) installation without issues. Any idea what I can troubleshoot from here?


texts: How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

texts: I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the session on username (messages and comments of friends and user itself on user wall). Please help me out.


texts: Explicitly update a table after sp_tableoption "text in row" advices wanted

texts:
I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set for these ntext columns, though when "select ntextcol1, ntextcol2 from table" the query takes up to 5min...crazy! But ofc when one knows which row to fecth, by its id which is indexed its fast!

To try to overcome this, I was trying to use sp_tableoption "text in row", 4000 but according to some Msft Technet reading, this will only be performed on existing records only when they're updated. So I was thinking in force an update in the rows so it would trigger the usage of the new option and hopefully have better performance.

This table as 100000 rows and seems scary stuff to do, so I was looking for some input from you and know if you have done it before, if it's a smooth process if any special advice...


texts: What is the privilege for account unlock in Oracle

texts:
I want to understand the privileges better, but I'm not able to find some useful info using Google.

I thought that I needed ALTER USER privilege to be able to unlock user and change password. But currently I'm on a machine, where I can change the password (also for other users), but I'm not able to unlock locked user. Why is that?

I tried also

SELECT * FROM USER_SYS_PRIVS ;

ant the result is:

TEST_USER   CREATE OPERATOR NO  NO
TEST_USER   ALTER SESSION   NO  NO
TEST_USER   CREATE SESSION  NO  NO
TEST_USER   CREATE DATABASE LINK    NO  NO
TEST_USER   CREATE SYNONYM  NO  NO
TEST_USER   CREATE INDEXTYPE    NO  NO
TEST_USER   CREATE MATERIALIZED VIEW    NO  NO
TEST_USER   CREATE TRIGGER  NO  NO
TEST_USER   GLOBAL QUERY REWRITE    NO  NO
TEST_USER   CREATE SEQUENCE NO  NO
TEST_USER   CREATE CLUSTER  NO  NO
TEST_USER   CREATE TYPE NO  NO
TEST_USER   CREATE PROCEDURE    NO  NO
TEST_USER   CREATE VIEW NO  NO
TEST_USER   CREATE TABLE    NO  NO

Error, when tried unlock:

alter user other_user account unlock
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges


texts:
Situation:
Large amount of data (millions to tens of millions of rows) in several tables that is not changed often, but when it does, there are usually lots of changes.

Tables each have a "summary" table associated with them that needs to be scheduled for update when any changes are made to main table.

Current Proposed Solution:
Update/Insert/Delete Trigger created on each main table that inserts the name of a table into a table called UpdatedTables.

A job is scheduled to run each night that checks UpdatedTables, updates the summary tables for the tables listed there, and then deletes the entries from UpdatedTables.

Questions:

Does my solution make sense? Is there anything wrong with it?
Could this cause issues if a large import/update was still going on when the job was scheduled?
Would it be worth disabling the trigger after the first insert and then re-enabling it after the job runs at night?  I know the per transaction cost is minimal, but I don't see the point in it executing 5 million times when it really needs to only run once.


texts: How to restore .wt backup file to local MongoDB?

texts:
This is a question that was asked before, but I have tried all solutions, and simply cannot get it right. I have spent quite some time researching before posting this question. I have looked at the official MongoDB documents and many other blogs.

How to restore a .wt MongoDB backup file to a local MongoDB database?


texts:
Always On availability group with two nodes, synchronous commit.

Redo thread contention on the secondary replica regularly creates a very large redo queue. I have confirmed the wait types are similar to the following:

https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/

In my case, this is one extended events session(captured when the redo queue was large) output grouped and aggregated like in the above link:

My question:

How can I find out the exact source of the DDL operation which is causing the LCK_M_SCH_M wait?


texts: Using UNION (ALL) in a WHILE loop with SELECTto merge the result set for each iteration

texts:
When I am looping with WHILE in SQL Server 2012, if I use a SELECT statement it prints the column name for each iteration. Can I UNION all?

Here is my sample query:

CREATE PROC spGetChar
    @start int ,@end int
AS
BEGIN
WHILE (@start<@end)
BEGIN
--Print CHAR(@start);

SELECT CHAR(@start) AS Character;

SET @start=@start+ 1;
END
END;


texts: How to Ensure of Data MariaDB Galera Cluster's Safety?

texts: MariaDB will send a copy of SST to every server which request for it even without checking whether it is in the gcomm:// list. So I can easily get other people's data by simply install a fresh copy of cluster and add the target server's address in gcomm:// list. Then how I can ensure my data's safety?


texts: I'm going to wrap database trigger's source code, command properly run but source not encrypt

texts:
I'm going to wrap my source of database logon trigger. i run the command,
"wrap iname='E:\block_tools_trigger_HR.sql' oname='E:\block_tools_trigger_HR.plb'".
plb file created without any error but code not encrypted.

Source code of trigger is given blow:

CREATE OR REPLACE TRIGGER block_tools_trigger_HR
AFTER LOGON ON HRMS.SCHEMA
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
IF UPPER(USERENV('TERMINAL')) NOT IN ('MOAZZAM-LT') THEN
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR --OR — Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR --    — SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --— PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR --  — Business Objects
UPPER(v_prog) LIKE '%EXCEL%'  OR --    — MS-Excel plug-in
UPPER(v_prog) LIKE '%SQLPLUS%' OR --     — SQLPLUS
UPPER(v_prog) LIKE '%FRMBLD%' OR
UPPER(v_prog) LIKE '%IFBLD60%' or upper(v_prog) is null
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login in '||v_prog);
END IF;
END IF;
END;


texts:
I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR:  deadlock detected (see the full error below).

How can I avoid this? Can I add a EXCEPTION which will return 0 in case this happens, I will prefer not to, but the bulk insert is very convenient for me.

CREATE OR REPLACE FUNCTION "univ"."gc_title_desc"(IN _title text, IN _desc text, OUT result_id int4) RETURNS "int4"
AS $BODY$BEGIN
LOOP
BEGIN
WITH sel AS (
  SELECT id
  FROM   univ.results
  WHERE  title = _title AND description = _desc
  )
, ins AS (
  INSERT INTO univ.results (title, description)
  SELECT _title, _desc
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins
INTO   result_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- inserted in concurrent session.
    RAISE NOTICE 'It actually happened!'; -- hardly ever happens
END;

EXIT WHEN result_id IS NOT NULL;
END LOOP;
END
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;

Error:

2015-02-25T15:11:15.078Z 3564 TID-oti3yx4ms WARN: PG::TRDeadlockDetected: ERROR:  deadlock detected
DETAIL:  Process 15507 waits for ShareLock on transaction 14912613; blocked by process 15690.
Process 15690 waits for ShareLock on transaction 14912617; blocked by process 15507.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (12728,26) in relation "uniq_search_results_title_description"
SQL statement "WITH sel AS (
    SELECT id
    FROM   univ.search_results
    WHERE  title = _title AND description = _desc
  )
  , ins AS (
    INSERT INTO univ.search_results (title, description)
    SELECT _title, _desc
    WHERE  NOT EXISTS (SELECT 1 FROM sel)
    RETURNING id
  )
  SELECT id
  FROM sel NATURAL FULL OUTER JOIN ins"
PL/pgSQL function univ.gc_title_desc(text,text) line 5 at SQL statement


texts: Why does MySQL not have hash indices on MyISAM or InnoDB?

texts: I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up with that?


texts: User on SQLAgentOperatorRole unable to edit jobs

texts:
My Active Directory account is on an AD group that is on msdb's SQLAgentOperatorRole. I'm able to list existing MSSQL Agent jobs, but I can only view their steps and schedules, I'm unable to edit them or create new.
I'm able to create new jobs, but on their step, when I select CmdExec, no proxy is listed.
If I remove this group from SQLAgentOperatorRole, no job is listed anymore.
When I log as sa I'm able to edit jobs and proxies are listed.
Am I missing something to be able to manage jobs?


texts: How to enforce uniqueness of compound primary key made up of two foreign keys to the same table?

texts:
I have two tables: a user table and a friendship table. Say the friendship table looks like this:

friendship
------------
user_one_id
user_two_id
other_fields

I need to enforce the uniqueness of the combination of values of (user_one_id, user_two_id) and disregard the ordering, so:

user_one_id | user_two_id
------------+------------
          1 |          2
          2 |          1  -- the DBMS should throw a unique constraint error when trying to insert a row like this one.

Another important point is that user_one_id represents the initiator of the friendship and user_two_id represents the recipient, so I cannot just make the "smaller" of the two ids user_one_id.

The question

Is there a way to do this using constraints or should I implement this some other way?


texts: Can I limit an index to only contain the newest relevant row?

texts:
I have a table in PostgreSQL 9.2 that records login sessions called user_login_session. This table contains a user_id, start_timestamp and end_timestamp columns, amongst others. This table has started growing as my system gains traction, and I can't purge old records for now.

I've defined an index on this table but I'm concerned about its growth over time. Is it possible to define an index which only keeps a handle to the newest login session row per user ID? Older rows are not accessed operationally.


texts:
I am trying to write a re-usable query and this is pseudo-code of what I am after.  Is this possible to do in Sql Server 2008 or do I just need to write a different stored procedure for each possibility?

Declare @acceptedvalues varchar(max), @whole12 varchar(500)

if @whole12 = 'MNM'
Begin
  SET @acceptedvalues = 'One', 'Two', 'Three'
End

if @whole12 = 'ALS'
Begin
  Set @acceptedvalues = 'Five', 'Seven', 'Nine'
End

Select
persID
,case when numinput NOT IN (@acceptedvalues) then 'Two' end as [validnumber]
FROM mixednuts

EDIT
I am trying to use a table variable - and this is my syntax, but it gives me an error of

  Msg 137, Level 16, State 1, Line 32
  Must declare the scalar variable "@acceptedvalues".

Declare @whole12 varchar(500)

DECLARE @acceptedvalues AS TABLE
(
    AValue int NOT NULL
)

Set @whole12 = 'MNM'

if @whole12 = 'MNM'
Begin
  INSERT INTO @acceptedvalues (AValue)
  VALUES ('1'), ('2'), ('3')
End

if @whole12 = 'ALS'
Begin
  INSERT INTO @acceptedvalues (AValue)
  VALUES ('5'), ('6'), ('7')
End

Create Table #MixedNuts
(
  persid int
  ,numinput int
)

Insert Into #MixedNuts Values ('1', '5'), ('2', '10'), ('3', '2')

Select
persID
,case when numinput NOT IN (@acceptedvalues) then 'Two' end as [validnumber]
FROM #mixednuts


texts:
Having problem using BCP via xp_cmdshell.

I exported the table data to a file as .dat but when I try to import the file to a new table, I am getting the below error. The SQL Server service and the account that I am using has full access on that drive. I am not sure why I am still having issues.

  Error = [Microsoft][SQL Server Native Client 10.0]  Unable to open BCP
  host data-file.

Here are the scripts that I am running.

exec master..xp_cmdshell 'BCP database.dbo.tabel OUT d:\tabledata.dat -T -c'
exec master..xp_cmdshell 'BCP database.dbo.tabelnew IN d:\tabledata.dat -T -c'


texts: How to add a fixed string to a mongoDB result like we do we in SQL Server?

texts:
We have this syntax with SQL server:

select name as 'database',
      'I_Love_SQL' as 'feelings'
from sys.databases

which will return results like this:

database    |   feelings
database1   |   I_Love_SQL
database2   |   I_Love_SQL
database3   |   I_Love_SQL
database4   |   I_Love_SQL

How can I achieve this with mongoDB? I have this query and I need to add some fixed fields for example ACTIVE, PT-BR, etc.

db.product.find({
    "company.cad": {
        $in: [" ", "14940"]
    },
    "tradeItemMeasurements.netContent.measurementUnitCode": {
        $ne: null
    }
}, {
    "_id": 1,
    "brandNameInformation.brandName": 1,
    "childTradeItems.gtin": 1,
    "company.cad": 1,
    "placeOfProductActivity.countryOfOrigin.countryCode": 1,
    "referencedFileInformations.uniformResourceIdentifier": 1,
    "tradeItemClassification.gpcCategoryCode": 1,
    "tradeItemDescriptionInformation": 1,
    "tradeItemMeasurements.netContent": 1,
    "tradeItemMeasurements.width.measurementUnitCode": 1
}).limit(1)


texts: Rule of thumb for tmp_table_size and max_heap_table_size MySQL properties

texts:
The title pretty much sums up the question itself: Is there a rule of thumb regarding the values for tmp_table_size and max_heap_table_size MySQL properties?

I've had a serious performance degradation which was caused by the fact MySQL used disk space to filesort a JOIN result.

Increasing tmp_table_size and max_heap_table_size to 3G solved the issue but this is more of a trial and error approach.

Is there a more solid way to calculate an appropriate value for these two?


texts: What is the correct way to ensure unique entries in a temporal database design?

texts:
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).

This is what I have, but it allows me to insert a new record for a store with a different effective date.

Table Definition:

/****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
DROP TABLE [Z_STORE_TEAM]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
BEGIN
CREATE TABLE [Z_STORE_TEAM](
    [STORENUM] [int] NOT NULL,
    [TEAM] [varchar](10) NULL,
    [EFFECTIVE] [date] NOT NULL,
    [FINISHED] [date] NULL,
PRIMARY KEY CLUSTERED
(
    [STORENUM] ASC,
    [EFFECTIVE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Sample Data:

INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date))
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL)
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL)
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)

Instead of Update Trigger:

CREATE TRIGGER [tr_ZStoreTeam_update]
   ON  [Z_STORE_TEAM]
   INSTEAD OF UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)
    SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE
    FROM inserted I
    INNER JOIN PCR.Z_STORE_TEAM ST
        ON I.STORENUM = ST.STORENUM

    UPDATE ST
    SET FINISHED = GETDATE()
    FROM PCR.Z_STORE_TEAM ST
    INNER JOIN inserted I
        ON ST.STORENUM = I.STORENUM
        AND ST.EFFECTIVE = I.EFFECTIVE
END

GO


texts: What Geopartioning or distributed SQL database can I use?

texts:
I want to split medical data on multiple nodes because I have Swiss, Europeans and Americans and I need to store data in citizen country.

I don't want to manage this function in App but in the SQL database engine.
The user’s table has a country code and automatically data are stored on good nodes/data center.

Do you have any solution to do this ?


texts: Find active values of SQLNET.ORA parameters on server

texts:
How can I find the active values of SQLNET.ORA parameters on the server?

I know how to find the SQLNET.ORA file but there's no guarantee the values in that file are actively used.  Parameter changes may require restarting the listener and sometimes even restarting the database.  For debugging I want a way to be 100% certain how the parameters are set, without restarting anything.

This is similar to this question, but I am looking for any method to find the current values.  It doesn't have to be a SQL query.

Here's what I've tried so far that leads me to believe it's impossible:

Tracing does not generate that data.
There does not appear to be anything in the data dictionary.
There's no information in the manual or support.oracle.com.
Offered a bounty but no takers. (But I'll offer and award another bounty if anyone can answer this.)


texts: Intermittent "access denied" (1045) message from MySQL

texts:
I'm importing a fairly large (100GB+) database from MySQL to PostgreSQL using pgloader. After a seemingly random amount of time the process dies with the following error:

An unhandled error condition has been signalled:
   Failed to connect to mysql at "localhost" (port 3306) as user "USER": MySQL Error [1045]: "Access denied for user 'USER'@'localhost' (using password: YES)"

That user has access to the database both before and after I get the error, so something goofy is going on. Sometimes it happens fairly early in migration. Sometimes it's near the end. Sometimes it happens while copying a table with lots of rows. Sometimes it happens on small tables.

The migration tool opens a new TCP connection for each table, so on a hunch I tried bumping the nofile limit for the mysql user up to 4096. My next attempt completed successfully, but subsequent tests have all failed.

My tests are failing on Ubuntu 14.04 running MySQL 5.5.41. Running the same process on OS X with MySQL 5.6.23 works correctly.

I can't find any errors logged anywhere (mysql error log, syslog or dmesg).


texts: Postgresql criteria for record to text conversion

texts:
Working on two servers, I found differences in the way Postgresql compose a record and converts it to text. I can't find which is the configuration involved in this difference.

As a test, I run the following query:

select ('Innovatività','CGC')::text, row('Innovatività','CGC');

On first server I got:

(Innovatività,CGC)  (Innovatività,CGC)

On the second server I got:

("Innovatività",CGC)    ("Innovatività",CGC)

On a third server check I got the same result as the second one.

Comparing the results, the second server enclose the string Innovatività with double quotes, the first server doesn't do it.

About the servers:

First (without double quotes):

version 9.4.14
lc_collate = en_US.UTF-8

Second (double quotes):

version 9.4.8
lc_collate = Italian_Italy.1252

Third (double quotes):

version 9.6.6
lc_collate = Italian_Italy.1252

Do you know why?

I know that lc_collate is different between the servers, but I don't know I can check if the problem is this (and in case I don't know how to fix it). I have tried the command set lc_collate = en_US.UTF-8 but the lc_collate can't be changed with set command.


texts: PostgreSQL COPY: is it always faster to delete and recreate indexes?

texts:
In this answer, Erwin Brandstetter recommends

  It is also substantially faster to delete indexes before a huge bulk INSERT / COPY and recreate them afterwards as it is much more costly to incrementally adjust indexes for every inserted row than to create them at once.

1) Is this true even when the table is already large?  If not, is there an easy way to estimate the point of diminishing return?  For example, I'm doing COPY of 30,000 records at a time into a table which already has say 100 million rows.  Each batch is relatively small, but on the other hand the total batches on hand to import at the moment will more than double the size of the table.

2) If I were to delete the index before COPY, would recreating it be faster if the COPY is done with records already in index order?

There is only one index on the table, which is a 5-column primary key.

I'm doing this on a non-production machine, and I've already disabled fsync, syncronous_commit, autovacuum, etc.  I'm executing the COPY operation from four separate processes concurrently on an 8-core machine.  I've got 12GB of memory, so I could set maintenance_work_mem to 1GB or more if it would help.


texts:
I've stumped everyone with the previous question about how rman would rather consume infinite disk space than remove obsolete backups. That leads me to this question:

datapump backups work (I have actually tested restoring them), and they are easy to manage unlike rman (if you want to dispute this, answer the previous question!) so why shouldn't I just disable the rman backups and consider that the solution?


texts: SQL Server - Cannot reorganize index on "extended_index" table

texts:
I have a maintenance plan that includes an index reorganization step.  I am getting the following error in the job history:

  Executing the query "ALTER INDEX [SpatialIndex-20180705-165942] ON [dbo..." failed with the following error: "The index "SpatialIndex-20180705-165942" on table "extended_index_113435478_384000" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Now, yesterday I found the index with that name on a table that was NOT named "extended_index_113435478_384000." I changed the spatial index to allow both row and page locks.  But I let the job run overnight and it still shows an error on the "extended index," which appears to be an internal table that I can't get at.

How to I resolve this error?


texts: identity value way off in a restored database

texts:
I have a table with a identity primary key column. Its last value is 5228491220 using IDENT_CURRENT.

I make a full backup of the database, restore it on a second server. (We backup and restore daily from one server to the other one.) Immediately I pull IDENT_CURRENT from this table and the value is 5228501279.

That is, the restore jumps the last value by more than 10000.

I can't make sense of it. Why would the last value of this identity column not be carried properly through the backup?

More practically, how do I make sure the value carries over properly?


texts: pacemaker and semi-synchronous replication

texts:
In the company I work we use a simple setup of a Primary-Secondary replication. Should the Primary break down for whatever reason, we do the switch manually. This also means, that MySQL hardly ever gets updated. I want to make it possible, to update the servers without downtime. For various reasons we do not want a (over-)complicated solution. So I'm wondering, to achieve my goal, can it be as simple as this:
A Primary-Primary replication with GTID enabled and semi-synchronous replication.
Pacemaker to switch a virtual IP from one server to the other, so I can stop one server to update. Then switch back and update the other server.
For the Primary-Primary replication I do not have auto-increment-increment configured differently. All write processes would use the virtual IP of pacemaker and therefore would write to only one host.


texts: does using optimizer_mode=first_rows_n for application that using Oracle database is a best practice?

texts: So I'm working in application that involving display of data in table/grid. And the grid will invoke paging query to oracle. So does by tuning my application to get the best query cost with optimizer_mode=first_rows_10 is always good and consider best practice with this kind paging search with html table or any table based approach application.


texts: Why my Postgres 9.3 database is logging crazily autovacuum alerts

texts:
The latest days, Postgres has started to log very frequently (about 100 logs per second!!!!) this kind of traces:

autovacuum: found orphan temp table "pg_temp_5"."p14528" in database "ma_prc_lim_absa_z5"

Is this a real problem?
If it isn't, how can I filter them?


texts: SQL Server 2012 compatibility with Microsoft Server 2016

texts:
This is just a really simple question before I steam roller ahead
and wreck everything.

I have ordered a server that will have Windows Server 2016 installed
however I have now realized that the tools we will be using only support
SQL Server 2012.

I'm not sure but I think Windows Server and SQL Server are completely
separate entities so would it still be OK for me to install
SQL Server 2012 on a Windows Server 2016 machine?


texts: Getting Error 10054 after renaming the server

texts: When I open SQL Server I have error 10054. Recently I changed the server name and I probably have this problem since. How can I solve it?


texts:
We are in the process of creating a web application using mysql as a data source.
We are using spring with hibernate as an ORM framework.
We have hundreds of entities with relations (manyToMany \ manyToOne etc..).

In the dev envioronment we currently use the hbm2dll in order to create the schemas from time to time. (after huge changes).

last week we encountered a deadlock happening (note that we do not use any explicit locking on our project). on a simple usage of the application.

removing the foreign keys from the table actually solved the issue.

So a couple of questions:

1) are foreign keys "evil"? if removing them from the DB did not affect the application logic and solved the issue - whats the point?

2) should we get rid of all of them?


texts:
I have a script that stores data in a temporary table to be exported by running BCP using xp_cmdshell.

The trouble is that BCP insists on exporting the data in Unicode and I need it to be in ASCII.

I've tried using a format file but I don't think I have the correct type.

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-

instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="line" xsi:type="SQLCHAR" />
</ROW>
</BCPFORMAT>

What is the correct type to save the text as ASCII?


texts: What are the benefits of using MySQL Cluster?

texts: I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?


texts: SQL Alert: 020 - SSPI handshake failure. Tracking the cause?

texts:
I have done my best to find an article that answers this question but I have not had much luck.

I manage the database across three domains. One domain has the ability to access the other two, but without a trusted connection (SQL traffic through firewall using SQL credentials). I have started getting a lot of SSPI handshake failures in my logs (and as such alerts). I know that a percentage of them are my fellow developers forgetting to use SQL Auth and the server legitimately failing to authenticate.

However I also believe that there is an application out there (or an ODBC setting, authenticated excel report, etc) that is causing some of the errors. I have tracked the problem down to our RDP farm, but not to the root application/etc.

My question is: How can I determine the application/remote-user/something that is causing the SSPI error so that I can resolve it once and for-all!


texts: Why does my query hang when I put one more id in where statement?

texts:
I have a query that looks like

select count(*)

FROM (joins)

WHERE customer_id in (select id from customer limit 117) AND ... (more statements)

It involves tables with several hundred million records, table sizes around 60GB including all the indexes. The result for 117 would be a count of 1965, the expected count for 118 would be 1986.

With the limit at 117 the statement returns in a few hundred milliseconds. When I put the limit up to 118 the query just sits there and doesn't return at all.

This is using an RDS Postgres instance, and running statements through IntelliJ. I've also read around that using large WHERE IN statements aren't as performant as joining on the ids through a temporary table instead, so I've edited the statement to use WITH, but the same hang between 117 and 118 exists.

I assume I'm hitting some constraint/limit with something more to do with hardware than the statement, I'm just not sure what with.

Edit: When I change the WHERE clause to be WHERE customer_id between 1 and 1000 the query is still really fast, and ids between 1 and 1000 have no gaps.

I've also since changed my RDS instance from a medium to an XL and I can now limit from 128, but 129 hangs. Nothing looks strange from the monitoring.


texts: Why do we need listener for Oracle EBS application server?

texts:
I'm using Oracle Application EBS R12 which connects to Oracle DB 10.2.0.4
Why do we need Listener.ora in Oracle EBS Application ?
If so, can anyone explain why EBS Application needs it ?


texts: Why is the result of a totally deterministic and all-encompassing "case" in select query in view nullable?

texts:
I have a table, with Id as the primary key.

create table Anything
(
    Id bigint not null primary key identity(1, 1)
)

When I look at this table in Object Explorer, I see this image of course:

As you can see, column Id is not null.

Then I create a dummy view on this dummy table:

create view IdIsTwoView
as

select
    Id,
    (
        case
        when Id = 2
        then cast(1 as bit)
        else cast(0 as bit)
        end
    ) as IdIsTwo
from Anything

But this time, in Object Explorer I see this result:

As you can see, in spite of this fact that my case clause is all-encompassing and covers 100% of all records, and has an answer for all records, it's nullable.

Why does SQL Server have this weird behavior? And how do I force it to be not null?

P.S we have an infrastructure to generate code dynamically, and this behavior causes us trouble and we have to manually go and change all bool? types in C# to bool.


texts: Looking for feedback on database design for availability

texts:
TL;DR - Is my approach of using separate date and time columns correct given that the availability of a user is set on a day-of-week basis and is independent of the calendar date.

I'm using PostgreSQL 9.5 and working on a booking / availability project where a user can set their daily availability for a given date range; for instance:

User: 1

Range: 04/30/2018 - Infinity

SUN:  12:30:00 - 16:00:00
MON:  08:00:00 - 23:59:59
TUE:  00:00:00 - 23:59:59
WED:  00:00:00 - 16:30:00
THU:  08:00:00 - 23:59:59
FRI:  12:00:00 - 23:59:59
SAT:  12:00:00 - 23:59:59

In order to accomplish this, I was considering setting up the following tables, and I would like feedback/constructive criticism on my approach:

users

    Column     |            Type             |                     Modifiers
---------------+-----------------------------+----------------------------------------------------
 id            | bigint                      | not null default nextval('users_id_seq'::regclass)
 email         | character varying           | not null default ''::character varying
 created_at    | timestamp without time zone | not null
 updated_at    | timestamp without time zone | not null
 first_name    | character varying(255)      |
 last_name     | character varying(255)      |

date_range

   Column   |            Type             |                      Modifiers
------------+-----------------------------+-----------------------------------------------------
 id         | bigint                      | not null default nextval('ranges_id_seq'::regclass)
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
 start_date | date                        | not null
 end_date   | date                        |
 user_id    | bigint                      | not null

time_range

   Column      |            Type             |                        Modifiers
---------------+-----------------------------+---------------------------------------------------------
 id            | bigint                      | not null default nextval('time_slots_id_seq'::regclass)
 created_at    | timestamp without time zone | not null
 updated_at    | timestamp without time zone | not null
 date_range_id | bigint                      | not null
 start_time    | time                        | not null
 end_time      | time                        | not null
 weekday_id    | integer                     | not null

The goal of which would be to return users who have availability within a given date, and time range.

Start Date: Monday, April 30th
Start Time: 10:00 AM

End Date: Wednesday, May 2nd
End Time: 2:45 PM

For instance, given the above input and initial dataset, I would want to perform a search which would return a  user record with an id = 1, because they are available within the full 52.75 hr range of the input parameters.

* I've neglected to include Time Zone related considerations for the sake of brevity.

I'm also considering using a daterange type for the start_date and end_date. And had considered using a tsrange for the start_time and end_time; however, tsrange includes the date, and I didn't think that would be the best option considering that time_ranges are date-independent – That being said, I'm open to anything which makes this more efficient / easier to navigate.

Additionally, I'm guessing I'll need to use relational division when building the query?

EDIT I've found some good tutorials on similar setups, but they have all dealt with bookings where the event has a set start and end date and time. My understanding of what I'm trying to accomplish (and perhaps that understanding is incorrect) is that, a user needs to set general availability on a day-of-week basis as opposed to specific calendar dates, in order to avoid having a ton of repeating ranges.

I'm totally open to other setups or even just links to resources which can help me gain a better understanding of how to approach this problem.


texts: Apportioning usage of PostgreSQL WAL to database/table/query

texts:
I have a PostgreSQL 11 database which hosts several databases, some of which are contain tables that are frequently inserted and updated. Recently the amount of data being written to WAL files has increased greatly (about 400%), though I believe that the number of inserts to the database has increased by only about 20%.
As a consequence not only are we using more disk, but our database performance now seems constrained by the writer performance of the disks the WALs are on.
The application is updated very frequently, and it may be that something in the application (which we control, and are able to change) has changed, which is causing less efficient inserts/updates to be made, but I don't know how we would identify that. There have probably been a many changes since this change In behaviour started, and several since it was first noticed.
Is there a way of determining which databases/tables/queries are writing to the WAL, and in what (approximate) quantity?


texts: Working Around Filtering Problems in Merge Replication

texts:
I am in the process of defining filters on a merge replication publication for our database.

The problem I am coming up against is that merge replication has these rules, I don't like to call them limitations because I can understand the purpose.

1) When creating an article filter you cannot include a subquery, or at least you shouldn't. If you do then it will appear to work the first time you sync, but if something changes in the subquery table the filter will not be re-evaluated.

2) When using a join filter you can only join two tables together.

The problem I am coming across is that the relationships in our database are more complicated than that. For instance here is one of our relationships,

user <- regions selected <- STREET LIGHTS -> settings (are we syncing street lights) -> user

Here is an example table structure to explain the above in more detail,

User Table,

Id    Username
1     petermc

UserRegion Table, (think of this as a security table, which region is a particular user allowed to see)

Id    UserId    RegionId
1     1         2
1     2         4

SyncSetting Table,

Id    UserId    Table
1     1         StreetLight

Region Table (this is just a look up table to show some example regions)

Id    Region Name
1     North Auckland
2     South Auckland
3     Central Auckland
4     Great Barrier Island

Road Table,

Id    RegionId    Name
1     1           Rosedale Rd
2     1           North Shore Rd

StreetLight Table

Id    RoadId    Last Replaced
1     1         2012-05-01
2     1         2009-06-03
3     2         2001-06-08

So in this case if I wrote out a select statement to apply my filtering for petermc it would look like this,

select * from StreetLight where
roadId in (select Id from Road where RegionId in (select regionId from UserRegion where userid = 1))
and exists (select 1 from syncsetting where userid = 1 and [table] = 'StreetLight')

So I am doing two things there. First filtering based on region to reduce a very large table into a smaller more manageable subset.

The second is specifying whether the subscriber is interested in the StreetLight table at all. If they are not then the subscriber should have an empty StreetLight table. This part is important because the publication will have a large number of tables in it, so it doesn't make sense to include stuff that the subscriber is not going to use.

Our biggest database has millions of records in some tables, and there will be a moderate amount of updates of these records too. We must get this filtering correct. The option to not filter those tables is not feasible.


texts:
I want to audit a specific SQL query in Oracle. However I think we can audit only users or table level.
For example I want to audit 'audit statements', such as who run the "AUDIT CREATE SESSION by username by access" query.
Thanks for supports.


texts: #1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

texts:
When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on.
For this tried to create same table with create SQL but it says

#1005 - Can't create table 'mydb.table1' (errno: 150)

Now I created same schema table with table name table2, this was successfully created
but as my application code is written for table1, I need to rename this table to table1 again on which gives me following error.

#1025 - Error on rename of table errno: 150

Also  tried to run alter table query with set foreign_key_checks = 0. But same error.

While searching I came accross below SQL,

SHOW INNODB ENGINE STATUS;

Which displays following message

141223 11:30:10 Error in foreign key constraint of table myahd/table1:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:,
CONSTRAINT "fk_key9" FOREIGN KEY ("updated_by") REFERENCES "users" ("userid") ON DELETE NO ACTION ON UPDATE NO ACTION

While there is no such table listed in my database with name table1.
Nor any such key is found with fk_key9.

May be I can delete this table1's idb or frm file, but unable to locate it.
Or may be there is some other solution.

Thanks in advance.


texts: How to design database with post sharing with public, specific users or hide from specific users

texts:
I am new to database design, so please ignore if i am asking very simple, but i have searched a lot about this but didn't found some helping material.
So, the question is, I want to design a database which can support million of posts, the main functionality is, user can post anything, like text or image or video(in short post like social media). but i am stuck with following functionality,

how to manage db to share posts with selected users
how to manage db to hide post from specific people.
How to share post with public.
How to re-share post by any user

Simple database design which I currently made is,
class User(models.Model):
 name = models.CharField(max_length=2000)
 ...continue

class Posts(models.Model):
    creator = models.ForeignKey(User, on_delete=models.CASCADE)
    post_type = models.ForeignKey(WatchlistType,null=True, on_delete=models.CASCADE)
    caption = models.CharField(max_length=2000)
    isDeleted = models.BooleanField(default=False)
    createdAt = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    modifiedAt = models.DateTimeField(auto_now_add=True, blank=True, null=True)

    def __str__(self):
        return self.caption

Can anybody help to design above mentioned functionality. i am confused how ot manage users when sharing with specific people or hiding from specific people.
Please note that, application have thousands of users and posts, so can anyone please help me to design a optimized database. any reference or help will be highly appreciated.
Thanks.


texts: Retrieve total available days in date range compared against multiple records

texts:
Given the following MySQL database structure for a booking system, how can I retrieve all available days between two user supplied dates?

| ID | code  | date_arrival | date_departure
| 4  | APT01 | 2015-04-10   | 2015-04-15
| 5  | APT01 | 2015-04-22   | 2015-04-29
| 6  | APT02 | 2015-04-11   | 2015-04-19
| 8  | APT02 | 2015-04-20   | 2015-04-25

For example: The user enters 2015-04-16 as their start date and 2015-04-28 as their end date. The available days over this period are 16th - 21st using APT01, and 26th - 27th using APT02

This means there are 8 available days out of those requested by the user. How can I query the data and get 8 as my result?


texts:
I have 3 tables with id as common field. In 3rd table I have Lineindex,line columns which has multiple value like following :

Table A:

id   name
----------
1    name1

Table B:

id  value Aid
-------------
 1  val1  1

Table C:

id lineindex   line   Aid
--------------------------
1   1          lin1     1
2   2          lin2     1

Now, I want to take record by joining these table in a single row like'

a.id   name   b.id   value   line1[line+lineindex]   line2[line+lineindex]
-------------------------------------------------------------------------
 1     name1  1      val1    lin1                    lin2

Like, the line values in table- C need to get in single row.
How can we achieve this?


texts: Database which can be setup without admin user

texts:
I'm looking for a database (relational would be preferred, but NoSQL is also okay) which can be run without an admin user. I want to setup the database with an admin user, but then delete the admin user forever and run the database only with non-admin users.
Is there anything like that for Postgres, MongoDB, MySQL, etc.?

EDIT: The goal is to make the database 100% safe from unrestricted deletions. The normal users should only be able to insert data, deletions and edits should never be possible, also not for the admin.

Thanks for the help!


texts: db INDEX on ORDER BY FIELD in very simple query does not help - still slow response?

texts:
I have very simple query which returns 200K records very slow (20 seconds).

SELECT * FROM TABLE ORDER BY ID DESC

If I do just

 SELECT * FROM TABLE

it returns quick result.

I created INDEX on that field ID (ALLOW REVERSE SCANS) but still returns very similar response. Where can be the problem? What can be the cause of stagnation for this query? I updated statictics and index table metadata.

Shouldn't INDEX help me on this ORDER BY FIELD?

My server has 8GB RAM.
Buffer pool has value of 128MB. I adjusted it to 1 GB but still no too much progress.

I am using DB2 database.

Configuration Parameters are with this values: (SORTHEAP) = AUTOMATIC(164) and (SHEAPTHRES_SHR) = AUTOMATIC(824).  (SHEAPTHRES) = 0. Should I maybe adjust them and on what values?

This is live system in production so I will be very thankful for help.

Thank you


texts: Is MySQL trigger for 100 Millions daily updates can impact data base cluster performance?

texts:
I have a MySQL (Aurora 5.7) table in data base which has

700 Million rows
9 Indexes
25 Columns
100 Million daily updates
and 5 Million daily inserts

Overall this table is very busy and now we need to run some extracts on this data base to generate reports .
The issue is not only this but the column involved in Extract is not indexed so it takes 5 to 6 hours to perform extracts.
Extracts is just basically we get counts based on dates which is not indexed and we can not create indexes as this can impact application .
As we don't have index query goes for full table scan and impacts the Aurora cluster and breaks the Global Data base replication .
Even though we run this query in DR reader nodes it impacts  the cluster and AWS Supports and Experts also has confirmed this .
So we dont have option to run extracts on main table and this is where we are exploring other options .
Option 1 :
Take daily snapshot restore and run daily report in new cluster.
This option looks very good but the issue is cost
we have 18 TB data base and my extras daily will run for 10 to 15 hours because 21 such reports has to be generated so monthly cost will be close to $ 7 k
Option 2 :
Takes Daily snapshot  and extras only required  tables for report to s3 and then run Athena query .
But the issue is export to S3 is taking long time and cost again is very very high because even though we export few tables from Snapshot we are getting charged  for complete snapshot ,18 TB export which is again 6 K $ per month .
Option 3:
Have trigger on this bust table and inserts all the inserts and updates to new table and then run report on new table.
I need help to decide which is best option and if we go with Trigger option then there will 100 millions triggers daily and this many trigger can impacts data base ?
I have also looked at using DMS on this table and moving data to another table but that would be continuous replication which again can impact Cluster .
NOTE : Sorry to say but we can not afford any downtime for the application
Please suggest .


texts: How to join all combinations from two tables with a common column

texts:
I have a list of suppliers who supply a list of countries. For example:

Supplier A supplies to:

USA
France
China

and another list of suppliers with their suppliers' factories For example:

Supplier A manufactures from:

UK
Germany

How can I build the list (in rows) of all possible combinations of (suppliers, countries, factories)? I have more than 10.000 suppliers. For example, for Supplier A:

USA-UK,
USA-Germany
France-UK
France-Germany
China-UK
China-Germany

Could someone please help me with this? I have been trying to work on it for weeks using Excel and then Access.


texts:
I am in the process of changing the character set for our database. I have a couple questions with regard to the in-place migration using the CSALTER script and performing the full import/export. According to the Oracle documentation, the use of the CSALTER script requires that

  it can be used only if all of the schema data is a strict subset of the new character set
  Source:Changing the Database Character Set of an Existing Databasee

The current character set is WE8ISO8859P1. However, I want to change it to AL32UTF8. Is WE8ISO8859P1 a subset of AL32UTF8?  And judging by experience, is it preferable to perform the migration using the CSALTER script as opposed to doing a full export/import?


texts:
I have difficulty understanding when to use .method() and when to use ::method() in SQL Server.

For example

For HierarchyID data type
I have seen HierarchyID::GetRoot(), but also @sample_node.GetAncestor(1);
For Geometry data type,
There is GEOMETRY::parse() that is used to insert rows. But there is also methods such as @sample_geometry.ToString() to output data with better readability.

What are the differences between them?


texts: What could cause "lagging" in SSIS execute SQL task?

texts:
Recently I noticed that the part of my ETL process loading the data into the staging area sometimes takes longer and sometimes shorter.

With the following query (executed in the integration services catalogs database) I was comparing two different runs and came to find out, that the recreate table statements are taking much longer (sometimes also the loading part, but I think the main problem is this). Here's the query to compare the different runs:

select
es1.execution_path,
es1.execution_duration as es1dura,
es2.execution_duration as es2dura,
(es2.execution_duration * 1.0) / es1.execution_duration * 100
from
catalog.executable_statistics es1
join catalog.executable_statistics es2 on es1.execution_path = es2.execution_path
where es1.execution_id = 239
and es2.execution_id = 10290
and es1.execution_path like '%create table%'
order by
--(es2.execution_duration * 1.0) / es1.execution_duration * 100 desc
es1.execution_path

Part of the result is this:

This corresponds to the following part of the ETL:

Please don't mind the additional green line in the screenshot, this is just messy formatting in Visual Studio. There are no more tasks running in parallel and the job is running around midnight, there's no other job running on the database at the same time.

These tasks are really simple. One of the Recreate table tasks for example looks like this:

IF EXISTS (
    SELECT * FROM sys.tables
    WHERE name = 'admin_perso_abteilung'
)
DROP TABLE admin_perso_abteilung
GO
CREATE TABLE admin_perso_abteilung (
    [id] int,
    [perso_abteilung] nvarchar(50)
)
GO

Anyway, the question is, since the query result suggests, that the whole process is stuck there for some time before executing the recreate table statements in parallel, what could cause this? What can I inspect further to narrow down the problem?

Since I'm more of a developer than an administrator I'm a bit lost here, please guide me a little. Thanks.


texts:
I am trying to dump a schema from Postgres 9.4, running on Cents 6.5, but I get an error:

  pg_dump: No matching schemas were found

Here is the command I am running:

pg_dump -U postgres -n "OLD-TODELETE-bushes" --verbose site > output_nf

I am a little confused by this as when I run select schema_name from information_schema.schemata, I can see the schema name.

If I run pg_dump for all schemas the OLD-TODELETE-bushes is dumped to file, as expected.

There are no error's in the Postgres logs.

Anyone have any ideas?


texts: Does partitioning help with lookups and inserts/updates by primary key?

texts:
I'm using MariaDB 10.1 and the default InnoDB storage storage, and I have a few tables with currently 10 to 100 million rows. These tables will keep growing a few  million per month, and it's mostly caching.

They either have a single-column primary key (BIGINT) or a composite primary key (two BIGINT columns) and no AUTO_INCREMENT, and I always insert, select or update by primary key. I also do a lot of joins by primary key, or selects WHERE PK IN (1, 2, 3, 4...).

Also, these tables receive lots of updates per hour, and I usually update them in batches of 5000 or 10000 at a time. We have more inserts and updates than selects for some of these tables.

I have 3 questions:

It seems to me that simple selects by PK returning 1 row (SELECT x, y FROM table WHERE pk = 123) will have no actual difference in performance with partitioning. Is that right?
What about joins or selects as WHERE PK IN(SELECT PK FROM ...)? Will it cause more scans to join a partitioned table than a single table?
Considering I usually do a lot of concurrent batches (multiple servers may send data at the same time) using:

INSERT INTO X VALUES (1, 'A'), (2, 'B'), ... ON DUPLICATE KEY UPDATE ... or
REPLACE INTO X VALUES(1, 'A'),  (2, 'B'),...

Will partitioning help with concurrent inserts and updates, say by being able to affect multiple partitions at the same time?

Thanks in advance.


texts: How do I start over? (newbie goof: now I need to start fresh with MySQL)

texts:
I'm just starting with MySQL (version 5.7.20 on Xubuntu 16.04), and in playing around with it I changed the root password.  Now I can't log in as root -- I either forgot the password or didn't set it to what I thought I did.  This is my home hobby system, so I'm the only user.

I may as well start over.  But it's not clear to me how to do that.  On Ubuntu there are about 9 or 10 mySQL packages involved, and I'm not sure I can uninstall all of them cleanly.  I'm happy to just trash the database if I only knew the commands to start with a new one.

I could use some advice, preferably from someone who knows how it works on Ubuntu and it's variants, as these packages are managed by Canonical and are not necessarily like even the Debian ones.


texts: NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

texts:
My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem.

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT NEWID() TEST_ID
) BB ( B )

With my inner join I had many rows for which I added to each a GUID using the NEWID() function, and for about 9 out of 10 such rows the multiplication with the 2-row virtual table produced the expected results, just 2 copies of the same GUID, while 1 out of 10 would produce different results. This was unexpected to say the least and gave me a really hard time trying to find this bug in my test data generation script.

If you take a look at the following queries using as well non-deterministic getdate and sysdatetime functions, you won't see this, I don't anyway-I always see the same datetime value in both final result rows.

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT GETDATE() TEST_ID
) BB ( B )

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT SYSDATETIME() TEST_ID
) BB ( B )

I'm currently using SQL Server 2008 and my work around for now is to load my rows with GUIDs into a table variable before finishing out my random data generation script. Once I have them as values in a table as opposed to virtual table, the problem goes away.

I have a workaround, but I'm looking for the ways to workaround without actual tables or table variables.

While writing this I tried without success these possibilities:
1) placing the newid() into a nested virtual table:

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT TEST_ID
    FROM (
        SELECT NEWID() TEST_ID
    ) TT
) BB ( B )

2) wrapping the newid() within a cast expression such as:

SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID

3) reversing the order of appearance of the virtual tables within the join expression

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS JOIN (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

4) using uncorrelated cross apply

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

Just before finally posting this question, now I tried this with success it seems, a correlated cross apply:

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT A
    FROM (
        SELECT 1 UNION ALL
        SELECT 2
    ) TT ( A )
    WHERE BB.B IS NOT NULL
) AA ( A )

Anyone have any other more elegant, simpler workaround? I really don't want to use cross apply or correlation for a simple row multiplication if I don't have to.


texts:
I am wanting to round down these numbers, and I have tried several different ROUND() functions but can not get it exactly right.  Take this sample DDL

Create Table #Fun
(
  value1 float
)

Insert Into #Fun Values
(1002313), (1888222), (1666222), (1222333)

Select
ROUND([value1], -5) As Rounded
FROM #Fun

Drop Table #Fun

In the below the left column is what SQL produces and the right is what I want it to be.  What must be altered in SQL in order to provide the output I am requesting?

1000000 = is fine
1900000 = 2000000
1700000 = 1500000
1200000 = 1000000


texts: Setting up SQL Server service broker from one database to multiple databases

texts:
I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance.

I have looked at the following examples, however, they only show me how to setup a SQL broker service between two databases:

Conversation between Databases in SQL Server - Service Broker
Setup service broker between two databases in SQL Server

I would be grateful if anyone could provide any assistance on this.


texts: Using Galera-Replication, what's the use in having a GTID associated with your backup?

texts:
On the topic of using a backupscripts the documentation for galera-cluster states that:

  You can use backups of this kind to recover data, but they are
  insufficient for use in recovering nodes to a well-defined state.

I struggle to understand what is meant by a well defined state and how this state could differ from a state where we just recovered our cluster with a backup we created with mysqldump.

Having the cluster in a "well defined state" as in having a GTID with a seqno bigger than zero won't be of any use if the changes to the other nodes can't be done via Incremental State Transfer (IST) --> saving time and traffic.

If the cluster is in such a dire state that we actually decide to recover from a backup, wouldn't there be a need to

a) assign a new clusterID and a seqno of 0 to the first node and

b) get the nodes back online via a state snapshot transfer anyway?


texts: Transaction log growing on a mirrored principal

texts:
I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the principal's status reverted to the usual Principal, Synchronized. The mirror, however, now reads Mirror, Synchronized / Restoring... I don't recall the Restoring... part usually being there.

The transaction log has begun to grow on the principal, as well, despite hourly log backups. This suggests to me that the synchronization process isn't freeing up transaction log space.

What's going on here and how do I fix it?


texts: MySQL keeps shutting down: /usr/sbin/mysqld: unknown variable 'set-variable=key_buffer_size=32M'

texts:
My MySQL keep shutdown automatically with this error report:

200405 23:31:39 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
200405 23:31:39 [Note] Plugin 'FEDERATED' is disabled.
200405 23:31:39 InnoDB: The InnoDB memory heap is disabled
200405 23:31:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins
200405 23:31:39 InnoDB: Compressed tables use zlib 1.2.8
200405 23:31:39 InnoDB: Using Linux native AIO
200405 23:31:39 InnoDB: Initializing buffer pool, size = 128.0M
200405 23:31:39 InnoDB: Completed initialization of buffer pool
200405 23:31:39 InnoDB: highest supported file format is Barracuda.
200405 23:31:39  InnoDB: Waiting for the background threads to start
200405 23:31:40 InnoDB: 5.5.62 started; log sequence number 14465811850
200405 23:31:40 [ERROR] /usr/sbin/mysqld: unknown variable 'set-variable=key_buffer_size=32M'
200405 23:31:40 [ERROR] Aborting

200405 23:31:40  InnoDB: Starting shutdown...
200405 23:31:41  InnoDB: Shutdown completed; log sequence number 14465811850
200405 23:31:41 [Note] /usr/sbin/mysqld: Shutdown complete

When I try to log into MySQL it says:

  DBI connect failed : Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I try to open /var/run/mysqld/mysqld.sock and can not found mysqld.sock

This happened after I tried to set key_buffer_size=32M by using webmin.

How to make it stop trying to set the key_buffer_size?


texts:
I get an error when I try to join the secondary replica to the Availability group thru Powershell while following the below Microsoft Documentation.

Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\<SecondaryComputer>\Instance" -Name "<AG Name>"

Error:

  Failed to join local availability replica to availability group ''.  The operation encountered SQL Server error 41106 and has been
  rolled back.  Check the SQL Server error log for more details.  When
  the cause of the error has been resolved, retry the ALTER AVAILABILITY
  GROUP JOIN command.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-an-availability-group-sql-server-powershell?view=sql-server-2017#ExampleConfigureGroup

I tried the T-SQL command thru SSMS as well.

USE MASTER
ALTER AVAILABILITY GROUP <AG Name> JOIN;

Same error.


texts: SQL Server sync databases between enterprise - web edition

texts:
We have an enterprise version of  sql server 2012. we need to synchronize its data to a web edition database. what would be the best option t do this ?  further to add on to this we need to provide high availability on the website. so the plan is to use 2 identical database on the web edition. only one will  be active at a time. periodically the enterprise version needs to push the data to the inactive database. make it active. and then sync the same data to the other one.

what would be the best solution to achieve the above ?


texts: "Test-SqlAvailabilityGroup" takes too much time

texts:
Can anyone explain me why the command Test-SqlAvailabilityGroup takes too much time ?
Is there a fastest way to check if a an availability group exists or not (preferably on PowerShell) ?
Thanks.


texts:
We upgraded our Percona XtraDB servers from version 5.6 to 5.7, and we noticed that the rotation slow query log files created after the initial file are empty. Is this something known in 5.7? How can we resolve this issue?

We also noticed that 5.7 does not have the /etc/mysql/debian.cnf file anymore that used to contain a username and password. Can that have anything to do with this?

Below is an example of these rotational slow query logs on one of our 5.7 servers:

-rw-rw---- 1 mysql mysql  1383157070 Sep 20 16:11 db-erp-1-slow.log
-rw-rw---- 1 mysql mysql           0 Jul 27 06:36 db-erp-1-slow.log-20170728
-rw-rw---- 1 mysql mysql           0 Jul 28 06:30 db-erp-1-slow.log-20170729
-rw-rw---- 1 mysql mysql           0 Jul 29 06:43 db-erp-1-slow.log-20170730
-rw-rw---- 1 mysql mysql           0 Jul 30 06:50 db-erp-1-slow.log-20170731
-rw-rw---- 1 mysql mysql           0 Jul 31 06:43 db-erp-1-slow.log-20170801
-rw-rw---- 1 mysql mysql           0 Aug  1 06:37 db-erp-1-slow.log-20170802
-rw-rw---- 1 mysql mysql           0 Aug  2 06:48 db-erp-1-slow.log-20170803
-rw-rw---- 1 mysql mysql           0 Aug  3 06:42 db-erp-1-slow.log-20170804
-rw-rw---- 1 mysql mysql           0 Aug  4 06:43 db-erp-1-slow.log-20170805

So after the initial log file, db-erp-1-slow.log, the following log files are empty.


texts: CentOS and Ubuntu differences with information_schema

texts:
I have a Vagrant VM running CentOS 6.5.  My localhost is Ubuntu 12.04, both running MySQL.  I have the exact same database on both (structured exactly the same, different testing seed data in both).  My issue is that running the following query gives different results between them:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'videos' OR
    TABLE_NAME = 'videos'

The output of describe videos; is exactly the same:

mysql> describe videos;
+-----------------+--------------+------+-----+-------------------+-------+
| Field           | Type         | Null | Key | Default           | Extra |
+-----------------+--------------+------+-----+-------------------+-------+
| id              | varchar(36)  | NO   | PRI | NULL              |       |
| created_at      | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| updated_at      | timestamp    | YES  |     | NULL              |       |
| title           | varchar(255) | NO   |     | NULL              |       |
| url             | varchar(127) | NO   |     | NULL              |       |
| provider_id     | varchar(36)  | NO   | MUL | NULL              |       |
| deleted_at      | timestamp    | YES  |     | NULL              |       |
+-----------------+--------------+------+-----+-------------------+-------+

My question is should there be any difference between the contents of INFORMATION_SCHEMA.KEY_COLUMN_USAGE for the foreign keys of a table between CentOS and Ubuntu?  I have no idea why that query would be returning different results.

Results on CentOS:

+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| videos     | id          | PRIMARY         | NULL                  | NULL                   |
+------------+-------------+-----------------+-----------------------+------------------------+

Results on Ubuntu:

+-----------------+-------------+----------------------------------+-----------------------+------------------------+
| TABLE_NAME      | COLUMN_NAME | CONSTRAINT_NAME                  | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------+-------------+----------------------------------+-----------------------+------------------------+
| presenter_video | video_id    | presenter_video_video_id_foreign | videos                | id                     |
| videos          | id          | PRIMARY                          | NULL                  | NULL                   |
| videos          | provider_id | videos_provider_id_foreign       | providers             | id                     |
+-----------------+-------------+----------------------------------+-----------------------+------------------------+

A little background in case it helps: The Vagrant VM and Ubuntu host share a Laravel repository folder, so the codebase is exactly the same.  The databases are built using Laravel's artisan migrate command (executing exactly the same code to construct the DB's).


texts: Why is my SQL Server giving out of memory errors when there should be plenty?

texts:
I have a production system running SQL Server 2019 Standard edition. It recently had a problem 3 times in 1 day where it became unresponsive until a reboot. Errors seem to point to memory limitations. 32GB is installed and the machine is dedicated to MSSQL. Max Memory set to 26GB.
(edited 2020-11-23 to remove some erroneous info re: MEMORYCLERK_SQLGENERAL.)
The most damnning info from the logs is a series of FAIL_PAGE_ALLOCATION errors.
Note: Since having the problem I've bumped up to a larger instance size (64GB)
Update 2020-11-22: The problem has reoccurred on the new larger instance. The memory errors began a few minutes after a scheduled backup of several databases. That may or may not be coincidence. It became nonresponsive and I forcibly restarted it.

I will start the MSFT support process as suggested by Josh and sqL_handLe.
I've made an Agent job (sproc here) to collect the results from dm_os_memory_objects every 5 minutes, based on sqL_handLe's query.
I've shared recent logs here. (I replaced the nl tags with actual newlines to make it easier to read.)
Disabled SSAS as suggested by Jana Sattainathan. I did confirm it was running and had 13GB "Min Working Set" as shown in the linked article, even though its actual working set was small. I think it's possible this subtle issue is the culprit, but if it's as dangerous as it seems, I'm very surprised I haven't run into it before. Jana, consider converting your comment to an answer?

The non-SQL load on this server is near-zero. The backup agent is just Ola Hallengren's TSQL scripts. The default SQL services are running but not in use. As mentioned, I've stopped SSAS. We occasionally use SSIS but not since the instance upgrade.


texts:
I wanted to know how riak handles two queries when the data set starts getting really large. Since riak buckets are just key space, everything seems like it lives in one massive index well bucket. I am finding it hard to plan for optimizing my queries as my data grows, keeping slow data away from fast data.

What is the best way to get keys into map reduce, because doing map reduce on a bucket may only enter the items from the bucket? Does it still go though all the keys in all buckets because the bucket is just part of the name of the key?

Is riak map reduce designed to be run in production for web page loads, or is it only really for taking aggregates of data and waiting just like I would with hadoop?

Edit

It is kinda what I am looking for, but what i mean is lets say i work with a lot of links how does it keep the link index fast, are all the links in the same index or different index Riak seems like its just buckets of buckets of information and that even if i have two different link type or links groups or what ever they will be in the same index meaning over time they will get slower even if one link type i keep it small for a reason


texts: SQL Network Name owned by Available Storage OwnerGroup in Server 2012 FCI

texts:
I did a simple get-clusterresource and noticed the 'owner group' is 'available storage'.  Not SQL Server (instance)for the SQL Server Network Name (instance).  I want to fix this ASAP but I'm having a hard time finding out how to move a owner group only.

Or actually from what I can tell the SQL Network Name (instance) isnt even supposed to be a 'network name'.  It's a instance.  I think this is a extra line I don't need and should remove this:

SQL IP Address 1 (clsnwname... Online                        SQL Server (I1)             IP Address
SQL IP Address 1 (clsnwname2... Online                        SQL Server (I2)              IP Address
SQL Network Name (clsnwname... Online                        SQL Server (I1)             Network Name
SQL Network Name (clsnwname2... Online                        SQL Server (I2)              Network Name
SQL Network Name (I1)       Failed                        Available Storage             Network Name
SQL Server (I2)              Online                        SQL Server (I2)              SQL Server
SQL Server (I1)             Online                        SQL Server (I1)             SQL Server
SQL Server Agent (I2)        Online                        SQL Server (I2)              SQL Server Agent
SQL Server Agent (I1)       Online                        SQL Server (I1)             SQL Server Agent

I was testing a new server that will be released at some point and noticed the Windows Updates were not installing SQL updates.  It stated it cannot update because the IP is offline.

I tested failover repeatedly and it always has worked, and continues to work as shown in the GUI with no issues and IP is up.  Replication and all connections are working fine.


texts:
UPSERT means INSERT and\or UPDATE, is there a standard name for a stored procedure that will INSERT if necessary and then SELECT?

e.g.  I want to get the IS of a row that matches columns and give me the ID, if nothing matched then I want it to INSERT a new row and return me the ID of that row.


texts: Quick look at how much RAM is allocated to SQL Server?

texts:
With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.

With SQL Server 2008, the Working Set or Commit Size never really goes above 500 MB, even though the SQLServer:Memory Manager/Total Server Memory (KB) perf counter states 16,732,760.

Is there a setting where it will actually show the server memory in the Task Manager?  Or is it a result of them changing how memory is used in SQL Server


texts: Backup-SqlDatabase: Cannot bind parameter 'InputObject' Error Being Thrown

texts:
I have a PowerShell script that is used in a fairly complex automated process. One thing it does is create a database backup, and for that we are using the Backup-SqlDatabase cmdlet. I am also using the -InputObject parameter for the Backup-SqlDatabase cmdlet so I can control the timeout of the backup operation. I had to do this because many of the backup and restore operations we are performing run longer than 10 minutes and were timing out. Here is the relevant snippet of code:

### Backup database. Changed to using SQL Connection object so I can control the timeout.
$SQLServerConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer
$SQLServerConn.ConnectionContext.StatementTimeout = $StatementTimeout

Backup-SqlDatabase -InputObject $SQLServerConn -Database $DatabaseName -BackupFile $DatabaseBackup

and here is the error that is being thrown:

Backup-SqlDatabase : Cannot bind parameter 'InputObject'. Cannot convert the "[server\instance]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
At C:\Users\Homer\Documents\testrestore.ps1:44 char:33
+ Backup-SqlDatabase -InputObject $SQLServerConn -Database $DatabaseName -BackupFi ...
+                                 ~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidArgument: (:) [Backup-SqlDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

(The square brackets are added in the error message. "server\instance" is just a string.)

The server where we are running this process used to only have SQL 2012 client tools installed on it, and it was working fine. This error started being thrown when we installed a SQL 2014 instance on the same server.

Based on the error I believe it is a compatibility issue between the SMO connection object and the Backup-SqlDatabase cmdlet InputObject parameter, but I am having a hard time trying to figure out exactly why. I've only found 1 other reference to this problem, but the thread kind of dead-ended with no real root cause.

Any thoughts or things to try to help me isolate it?


texts: Show the error message, after I accidentally clicked "Yes" on "don't show error again"

texts:
I was trying to alter an existing Maintenance Plan, but received an error on doing so. Before actually reading it I clicked it away, too late realizing that the text said "Do you wish to hide this error message in the future?"

I tried logging out/logging in, resetting my settings, browsing through Tools > Options but the error won't show again. How can I reset my earlier decision and show the error message?

It's Microsoft SQL Server Management Studio version 11.0.2100.60.


texts: mongoDB: Limit user to find documents only by ObjectId

texts:
I'm using mongoDB version 4.0.9 for various things. One of them is storing and retrieving files in mongoDB via GridFS. For that purpose, I have created a role/user in mongo with the privileges "createCollection", "find", "insert", and "createIndex".

However, I would like to limit the user's reading rights to only find documents by their ObjectId because this is all I need: The mentioned mongoDB user gets the ObjectIds from another data source and retrieves documents only by their id.

Right now, that user could list all documents via the find command. That means if an attacker was able to hijack that user's account, they could read everything.

If, however, there was a way to limit the user's rights to only read documents by their id then the attacker would be forced to brute force the ObjectId and that could easily be detected in the log files.


texts: Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

texts:
I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for indexes which are longer than the 191 characters, which the documentation seems to say is the max. I'm running MariaDB 10.x which I believe matches up with MySQL 5.7, and the documentation confirms the limit when using utf8mb4_unicode_ci collation and compact as the format.

I'm finding varchar(200) and varchar(255) indexed fields, e.g. wp_postmeta, the field meta_key is varchar(255). Am I wrong or confused on this? The db is using the InnoDB engine. Thanks for any clarification.


texts:
I have a C++ application that connects to SQL Server 2008 through FreeTDS unixODBC on an RHEL box.

However, i am unable to insert supplementary characters to the database through FreeTDS unixODBC.

[FreeTDS][SQL Server]Error converting characters into server's character set. Some character(s) could not be converted

This is the query that my application is hitting

INSERT INTO mytable (SAMPLE) VALUES(N'乕乭乺丕')

== version details ==
unixODBC version - 2.3.0
freeTDS version - 0.95

I can successfully insert the characters in BMP.
This characters are inserted perfectly fine.
INSERT INTO mytable (SAMPLE) VALUES(N'乕乭乺丕')

While using Microsoft SQL Server Native Client for Linux, the supplementary characters are inserted properly.

== freetds conf ==
[mysever.mydomain.com]
host = mysever.mydomain.com
port = 1433
tds version = 7.3
debug flags = 0xffff
dump file = /tmp/dump.log
dump file append = no
client charset = UTF-16
use utf-16 = yes

unixODBC v2.3.0 has been built with

./configure --prefix=/usr/local/ --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE

FreeTDS v0.95 has been built with

./configure --prefix=/usr/local --sysconfdir=/usr/local/etc --with-tdsver=7.3


texts: Installed latest patch, now Oracle.DataAccess.dll is throwing Data provider internal error(-3000)

texts:
The latest Oracle patch: http://www.oracle.com/technetwork/security-advisory/cpujul2017-3236622.html has been installed on the server running version 12.1.0.2.0.

We have .Net applications running directly on the same machine which uses the Oracle.DataAccess.dll which was at version 2.121.2.0 but the patch seems to have upgraded this to version 2.121.2.0 RELEASE 4 like so:

and now we get which appears to be a generic error:

  Data provider internal error(-3000) [System.String]

  OracleException (0x80004005): Data provider internal error(-3000) [System.String]]
     Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable) +1909
     Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src) +53
     Oracle.DataAccess.Client.OracleConnection.Open() +5393
     NHibernate.Connection.DriverConnectionProvider.GetConnection() +134
     NHibernate.Impl.SessionFactoryImpl.OpenConnection() +65

Any ideas where I can even start to look? I am suspecting version mismatch but I can't understand why that would happen.


texts: which mysql version to use - 5.1 or 5.5?

texts:
I am designing a new database. There are going to be around 1000 write queries per second. There may be as much as 10000 read per second.

Which MySQL version is recommended?

I came across a post on this site which says that 5.5 might be slower than 5.1 in some cases but some tuning will make it good enough. If tuning is done, then is it advisable to use 5.5? Or 5.1 performs better?

Edit

Here's the link to the post I referred to above - https://stackoverflow.com/questions/9202825/mysql-5-5-perfomance