texts: Why does replication with SSL fail on my MySQL database in AWS RDS?

texts:
I'm trying to replicate from AWS RDS to my own server. It works without SSL. Whenever I include the SSL property to the slave, it breaks with this error:

  error connecting to master
  'user@xxxxx.us-west-2.rds.amazonaws.com:3306' - retry-time: 60
  retries: 86400

I can log in with SSL to RDS using mysql client without problems:

mysql -h xx.rds.amazon -u user -p --ssl-ca=rds-ca-2015-root.pem --ssl-verify-server-cert

This is the STATUS:

     Slave_IO_State: Connecting to master
                  Master_Host: xxxxx.us-west-2.rds.amazonaws.com
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.001453
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin-changelog.001453
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: DB
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ssl/rds-ca-2015-root.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2026
                Last_IO_Error: error connecting to master 'user@xxxx.us-west-2.rds.amazonaws.com:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:

Again, I want to highlight that REPLICATION works well without using SSL, and SSL works well without using REPLICATION.


texts: Using MySQL Workbench, what is a good way to do development on an alternate server and copy changes to the main server?

texts:
This is a noobie question. I'm taking over a database project from someone who recently left the company and I'm looking for advice on how to do some development on a test platform before making changes on the active server. I've got MySQL installed on both the server and my local machine, with MySQL Workbench as the interface to both. Apache and PHP are also installed on both platforms.

I think what I want is a local copy of the schema (including data) that I can occasionally synchronize with the server. So when I want to do any development I would:

pull any changes from the server
make changes locally and test them out
then copy the modifications back up to the server.

Is that what people do in practice?
If No: What is the recommended way to do development without messing up the existing database?
If Yes: Can you offer some details on how to do this with MySQL Workbench.

Thank you, and please let me know if you need any more information.


texts: How do I stop a master from being a master in mariadb

texts:
I have a database which used to be setup as a master in a topology. Now I want to remove it as master, and make it a replica. How do I do this?
Is there something similar to how to stop slave but for master? For slaves I could do:
STOP SLAVE;
RESET SLAVE ALL;

I have tried STOP MASTER; and RESET SLAVE MASTER; but those does not exist.
What would the corresponding commands be for master?


texts: How this simple Stored Procedure ended up in Deadlock

texts:
Thanks for all and this forum is really helpful.

After few minutes of system running we get “Try restarting the transaction.”
And its end up in deadlock.

We use MySQL 5.5.28 and the Java code calling this Stored Procedure is in synchronised block. However, I am still we getting this deadlock. When I ran, show engine innodb status;, I see the following query in deadlock:

INSERT INTO Site_Msg_Tag (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
        VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);

Please refer below for entire Stored Procedure and the table it's updating:

CREATE PROCEDURE sp_CreateCtrMsgTagIfNotExist ( siteId INT, logTimestamp DATETIME, IntervalId INT, digestMatched TINYINT(1), OUT returnpk INT )
BEGIN

   SET returnpk = 0 ;
   START TRANSACTION;

   SELECT Site_Msg_Tag_Id INTO returnPK FROM Site_Msg_Tag WHERE Unit_Id = siteId AND Log_Timestamp = logTimestamp
   AND Interval_Id = IntervalId ;

   IF (returnpk IS NULL OR returnpk = 0  )
   THEN /*1*/
        INSERT INTO Site_Msg_Tag (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
        VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);

        SELECT LAST_INSERT_ID() INTO returnpk;
    END IF;/*1*/

    COMMIT;

END |

DELIMITER ;

Table :

| Site_Msg_Tag | CREATE TABLE `Site_Msg_Tag` (
  `Site_Msg_Tag_Id` int(11) NOT NULL AUTO_INCREMENT,
  `Unit_Id` int(11) NOT NULL,
  `Log_Timestamp` datetime NOT NULL,
  `Interval_Id` int(11) NOT NULL,
  `Digest_Matched` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`Site_Msg_Tag_Id`),
  UNIQUE KEY `ix_Site_Msg_Tag_Ts_Unit` (`Unit_Id`,`Log_Timestamp`,`Interval_Id`),
  UNIQUE KEY `ix_Site_Msg_Tag_Unit_Ts` (`Log_Timestamp`,`Unit_Id`,`Interval_Id`),
  KEY `FK_Site_Msg_Tag_Interval_Id` (`Interval_Id`),
  CONSTRAINT `FK_Site_Msg_Tag_Interval_Id` FOREIGN KEY (`Interval_Id`) REFERENCES `Interval` (`Interval_Id`),
  CONSTRAINT `FK_Site_Msg_Tag_Unit_Id` FOREIGN KEY (`Unit_Id`) REFERENCES `Unit` (`Unit_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

I saw in this thread Will these two queries result in a deadlock if executed in sequence?

in that they solved the issue by writing the query in more efficient way any suggestions ? . Thanks in advance.


texts:
The Above screenshot is my table structure..

Invoice Table

CREATE TABLE BASE_TInvoice
(
InvoicePaymentId BIGINT IDENTITY(1,1) NOT NULL,
PaymentDate DATE NOT NULL,
InvoicePaymentMethod VARCHAR(45) NOT NULL,
IsCashPayment BIT NOT NULL,
Note VARCHAR(5000) NULL,
IsRecordStatus BIT DEFAULT 1 NOT NULL
PRIMARY KEY(InvoicePaymentId)
)

Cash Inovice Table

CREATE TABLE BASE_TCashInvoice
(
CashInvoicePaymentId BIGINT IDENTITY(1,1) NOT NULL,
InvoicePaymentId BIGINT NOT NULL,
CashAmount  MONEY NULL,
CashReceived MONEY NULL,
CashBalance MONEY NULL,
IsRecordStatus BIT DEFAULT 1 NOT NULL
PRIMARY KEY(CashInvoicePaymentId)
)

I have used Right Join to retrieve data.

SELECT BASE_TInvoice.InvoicePaymentId, BASE_TInvoice.PaymentDate, BASE_TInvoice.InvoicePaymentMethod, BASE_TInvoice.IsCashPayment, BASE_TCashInvoice.CashAmount, BASE_TCashInvoice.CashReceived, BASE_TCashInvoice.CashBalance
FROM BASE_TCashInvoice RIGHT OUTER JOIN BASE_TInvoice ON BASE_TCashInvoice.InvoicePaymentId = BASE_TInvoice.InvoicePaymentId
WHERE (BASE_TInvoice.IsRecordStatus = 1) AND (BASE_TCashInvoice.IsRecordStatus IS NULL OR BASE_TCashInvoice.IsRecordStatus = 1) AND (BASE_TInvoice.InvoicePaymentId = 1)

The above query retrieving data fine, the bellow screenshot..

Once i set the BASE_TCashInvoice Status (IsRecordStatus) false and retrive data i'm getting blank, the below two screenshots.

I think the problem is in the where clause (BASE_TCashInvoice.IsRecordStatus IS NULL OR BASE_TCashInvoice.IsRecordStatus = 1)

How to retrieve exact data once BASE_TCashInvoice record InvoicePaymentId 1 and  IsRecordStatus went false need to display only the BASE_TInvoice table record only. Answer should be like below screenshot


texts: Creating clustered primary key index with ONLINE option

texts:
I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns  from which it is composed I need to re-created them or:

Drop the index
Change the column type
Create the index

I am wondering if I am using the ONLINE hint, the table will be blocked for inserts. In the documentation is said that:

  Long-term table locks are not held for the duration of the index
  operation. During the main phase of the index operation, only an
  Intent Share (IS) lock is held on the source table. This enables
  queries or updates to the underlying table and indexes to proceed.

Basically, the index is created for 20 minutes, so I need a way to guarantee user experience is not blocked. The alternative is to have separate window for maintenance in which the database to be in single user mode.


texts: POSTGRES Compare a json arrays against another array

texts:
I have a postgres jsonb column called items having

 [{"id":"","name":""},{"id":"","name":""}]

I need to compare this whole column with the data

 [{"id":"","name":""},{"id":"","name":""}]


texts:
I configured master slave replication and it worked fine however it stops working after 5 min or so! Although slave status show that replication is fine, however whatever I insert in master isn't replicating in slave

This is the tutorial I followed
 http://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/

Things only start replicating again when I run the command service mysqld restart on slave

master my.cnf

slave my.cnf

P.S I HAVE JUST ADDED "bind-address" in both of these cnf to check if it works


texts: Database Designs Alternative to Polymorphism in Establishing Relationships

texts:
I'm working on a database which handles replacement of parts in a machine.

Rules

A machine has many components
A machine has a set of assembly instructions
Many components have many part (screws,
bolts, etc)
Parts can substituted by components, there are no constrictions on components to parts they substitute.
Components behaving as substitutes require a special set of instructions

Bonus: Components can also swap out components

I've currently implemented polymorphism. Below is the sketch, I'm curious in other approaches. While I've scratched at Martin Fowler and some other literature, I've not been able to complete a visual of another approach.

I'm primarily dissatisfied with the duplication of fields for component_parts, and substitute_parts.

How would I go about establishing a schema achieving the above rules, while avoiding a straight shot towards polymorphism?

Current approach

    machine
    -id (pk)
    -name

    machine_directions
    -id (pk)
    -machine_id (fk)
    -step
    -direction

    components
    -id
    -machine_id (fk)
    -name

    component_parts
    -component_id
    -part_id
    -part_type (part, or substitute_component)
    -amount

    substitute_component
    -id
    -substitute_part_id (fk to relate to part)
    -name

    substitute_directions
    -id (pk)
    -substitute_component_id (fk)
    -step
    -direction

    substitute_parts
    -component_id
    -part_id
    -amount

    parts
    -id
    -name


texts: How to prevent specific applications from connecting to Oracle?

texts:
Is there a way to prevent certain applications (e.g. MS Excel or Access) from connecting to Oracle database?

A bit of context:

My company uses an Oracle database as our live production database. We don't want any Excel or Access files being linked to this database. I don't really care if users use something like Toad to run a few queries by hand but I don't want a bunch of Excel or Access files linked to this database. We replicate this database and all users who need this data should get it from the replicated database, not the live database.

I can of course view the list of sessions and what program was used to connect with this query, but I'm not sure how to prevent the sessions from being opened in the first place:

SELECT SID,SCHEMANAME,OSUSER,TERMINAL,PROGRAM FROM GV$SESSION

The PROGRAM column of this query will tell me if something is opened with Excel or Access.


texts: How to fine tune a huge problematic Heap Table?

texts:
Problem:
A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition).
Facts about the problematic Table :

Heap Table without index.
450 GB of Size.
More than 190 million records.
More than 900 MB/s Physical I/O activity every time a simple SELECT
Query executes upon. Worst if the query includes WHERE clause.
Queries executed upon this table are causing 4-5 minutes delays on the application UI.

Suggested solution by the application/system manufacturer:

Convert the Heap Table to a Clustered Index Table.

Suggested performance tuning steps with respect to suggested solution:

Truncate db table (agreed with customer to delete records older than 1 year).

Don't Delete Records. Copy records created in last 1 year to a temp table. Truncate the original table, and then INSERT the records you have copied from the temp table to the emptied original table.

Make a de-fragmentation on the db table.
Migrate the db table from heap to clustered table.
Create a Clustered Index.
Create a maintenance job and schedule it to re-build the clustered index periodically.

My question to community:
How would you improve the performance of such a problematic table? What would you add to or remove from the suggested performance tuning steps?
Much appreciated and thanks a lot in advance.
Edited from here on:
OK, I didn't want to reply to all comments 1 by 1. So I'm editing the question and adding some more information.
1st : I am not an expert DBA. I would say an advanced beginner. That's why I am here to learn from the community as much as I can.
2nd : We just got this customer 2 weeks ago, and I have not got full access to the environment myself. This information (facts) about the problem, about the table is directly from the customer's sys admins.
3rd : There for, I have no idea why the table is Heap in the first hand, without an index, and they let it to grow this much without any partitioning and what so ever.
4th : Suggested Solution and Suggested Steps are not my own but directly from the manufacturer of the whole system (it's a well know Unified Communications System and the manufacturer is the best in this area. I guess you all know which one).
5th : I will hopefully have a full access to the environment next week and before that I just wanted to share what I got in hand and try to get as much opinion/suggestion as I could prior to going into this mess.
6th : Because unfortunately I am the one who is going to take this mess from here and try to make it better and smoother working mess.
Thanks to everyone who already spare some time and replied. I already got some insight. And I will definitely share more info as soon as I have more in hand.


texts: Inserting into mysql table with archive engine "duplicate key" error

texts:
I am trying to archive some files using mysql archive engine. I am using this query to insert file contents:

insert into test_table (id,arch) values (123,'FILE_CONTENT')

After inserting 2 records, I get "duplicate key" error from mysql for a key that doesn't exist in the table. I checked it like 5 times but the record is not there. I selected count(*) for the duplicate id and the result is 0.

I checked the same code with an innodb engine and it works fine. Can anyone tell me what the problem is with the archive engine?

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `arch` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=100175977 DEFAULT CHARSET=utf8mb4;


texts: How to take backups of MySQL databases, explicitly based on PyMySQL

texts:
I am looking for a purely Python solution to take backups of every MySQL database on localhost. I already use PyMySQL to run queries on these databases. Any help is highly appreciated.

EDIT

After some search, I ended up with the following non-python-based solution.

import os

x =\
    "../../../mysql/bin/mysqldump\
    --host=localhost\
    --port=3306\
    --databases ****\
    --user=****\
    --password=****\
    --default-character-set=utf8\
    --add-drop-database\
    --add-drop-table\
    --add-locks\
    --complete-insert\
    --extended-insert\
    --lock-all-tables\
    --create-options\
    --disable-keys\
    --quick\
    --order-by-primary\
    --set-charset\
    --tz-utc\
    > dump/test.sql;\
    cd dump;\
    tar -zcf test.sql.tar.gz test.sql;\
    rm test.sql;"

os.system(x)

So far so good but I have some questions regarding the parameters as well as the execution.

Regarding my first concern, I just want to make sure that all of them are required and do not cause any conflicts when used together. Ultimately, I would like to make a very robust and consistent dump file, with millions of records, that creates tables, databases, and inserts data. The good thing is that making the database not-available for a while is not a problem for me. My only goal is to make a robust and consistent dump file.

Regarding my second concert, I would like to know if a) the os.system(x) python function is the recommended way to execute Unix commands through Python (as opposed to subprocess.Popen() that most people use), and b) how to get informed when one of these commands goes wrong and if so throw an exception.

Any ideas?


texts: Get slave status using sql query inside if-else condition

texts:
How can I stop the slave if it is already running using sql query statements only?

So far I tried to do something like below but getting sql syntax error. I think it is due to the borders of result in conditional statement.

if ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS
      where VARIABLE_NAME = 'slave_running')=ON)
     then
         STOP SLAVE;
end if;


texts: Is it possible to use txid_current value for sync

texts:
I need to expose my data through Rest API for a mobile offline experience.
The idea is basically that a mobile client downloads all data to his device. after a week he wants to download only the changes into his device. each mobile client synced to a different point of time.
My problem is how to create a deterministic cursor for the client which they will send it on their next sync request and I'll be able to figure out the changes since their last sync.
Assumptions:

Clients tolerate data duplication.
My database is insert-update only (never delete).

Possible solutions:

Logical Decoding
It's relatively (to other solutions we have in mind) hard to create a plugin and it doesn't fit our needs because our permission model, each client needs to get only rows he is permitted to see.
txid_current + cmin value.
Save the transaction id on each row to create incremental value, cmin is needed to create a more precise cursor (many rows may have the same txid, but only one row has a specific txid + cmin pair).
Use xmin only, and re-sync (sync from scratch) user's upon wraparound. (according to the epoch counter).
Add SERIAL column/sequence, and bump it up manually or with triggers, it won't make us have to serialize our transaction if we are using Overlapping Sync as describe below.
Serialize all transactions that act on the most top resource in my model (database), which is least preferred because of the performance and complexity it introduces to the application code.

Currently, I really want to use option 2, because it seems like the simplest solution.
How I'm going to handle Consistent Ordering:
The problem:
If the client syncs up to the latest visible (committed) change there may be active transactions that include earlier changes that are not yet visible.

The solution:
Overlapping Sync the clients, Sync up to the latest visible change but keep track of what the latest safe change was. On the next sync, ask for changes since the lastest safe change rather than the latest visible change.

Is option 2 valid? (under the assumption above and the Overlapping Sync solution), if not, what is the next simplest solution? there is another solution which I didn't think about?


texts: setup dblink in PostgreSQL to get data from oracle

texts:
We tried setting up oci dblink and setup oracle home in PostgreSQL environment.
Still we are unable to query using the database link.
It prompts oracle_home  or guc setting or oracle library path must present in path at the time of you start the server.
We have created the server and done the user mapping.


texts: How do I perform a UNION query on a dynamic list of tables?

texts:
My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>.

Each of these tables has the same data structure.

I also have another table in which I store the name of each table for future reference.

The following table name is 'content_types' and provides reference for other tables:

+-----------------+
| id | table_name |
+-----------------+
| 1  | _ct_table1 |
| 2  | _ct_table2 |
| 3  | _ct_table3 |
| 4  | _ct_fdfsf  |
| 5  | _ct_535dgf |
...
+-----------------+

The following are example to the referenced tables in 'content_types'.'table_name':

_ct_table1:                              _ct_table2:
+-----------------------------------+    +-----------------------------------+
| id | title | subtitle | date      |    | id | title | subtitle | date      |
+-----------------------------------+    +-----------------------------------+
| 1  | fddfs | ...      | 2012-08-22|    | 4  | fddfs | ...      | 2012-03-18|
| 2  | bdsf  |          | 2012-08-22|    | 5  | b4sf  | dfs      | 2012-07-20|
| 3  | sfdsd |          | 2012-08-22|    | 6  | sfdsd |          | 2012-03-12|
+-----------------------------------+    +-----------------------------------+

Is it possible to create a query with a dynamic UNION statement based on the tables listed in 'content_types'.'table_name'?

Something like this:

select id,title,subtitle,date
from
    (
        select table_name
        from content_types
    );


texts:
I'm trying to log in to a mariaDB database which is (at least the vendor of the product that it was installed for tells so) "protected" by SSL.

I tried several tools but none of them works fully.

MySQL Workbench exits silently when I try to connect but when I choose the "reverse engineering" menu entry it is able to extract and display the schema.
HeidiSQL fails with Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)
dBeaver shows the same: Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)
Database Workbench fails with the same error as the others: Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)

I am sure that the credentials are typed correctly because I copy them from a notepad window and they work with MySQL Workbench.
All tools are run with the current version available, community or free and not paid)

Are there different ways to authenticate with a MariaDB server ? Basically MySQL Workbench seems to work the best, but because it fails silently with a "full" connection this is also not feasible.


texts: What is the standard time delay taken for the data to get reflected in standby database when a new data is inserted in primary database

texts:
I've configured Data guard setup using data broker. When I'm inserting new data to the tables in primary database which is suppose to reflect in standby database. When I checked those data's in standby database it was not reflected there. So, I've restarted the database and opened it in read only. I've used the below command to avoid the delay:

SQL> alter database recover managed standby database nodelay disconnect from session;

But still a lot of time is taken to get reflected there in standby database.

What will be the time delay taken to get the data's from primary to standby?

Is this the standard time delay taken for a data to get reflected in standby database. Or do I have missed anything there in configuration.

Is there any way to sort it out?


texts:
I have a query that I want to save as a view because it will be used on a different system that we don't have access to. The query basically gets running balances on debit and credit columns from a table.

The query works fine,here is the query

SELECT id,deb_id,trans_date,trans_ref,dr,cr,
    @bal := @bal + (dr -cr) AS `Balance`
FROM debtor_transactions a , (SELECT @bal := 0) var
ORDER BY a.id ASC

But I cannot create a view from the above query, I keep getting this error.

Error Code: 1351
View's SELECT contains a variable or parameter

How can I get around this, or is there a way I can rewrite my query without the variable and still achieve the same result?


texts: Alter table add column query blocking a table or taking so much time

texts:
I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, I'm really confused about what's happening exactly.

ALTER TABLE MYTABLE1 ADD MYEXPORT9 NUMBER(38,10) DEFAULT 0;

This simple query doesn't finish yet and using the following query to see if the query is blocked or blocking I found it. Query:

SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
s.logon_time,
p.spid,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER
FROM v$lock l,dba_objects o,v$session s, v$process p
WHERE l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND p.addr=s.paddr
AND l.TYPE IN ('TM','TX');

And looking for in the v$session table I see the following line:

29  ANC oracle  46465270    clay925p6       sqlplus@myserveur (TNS V1-V3) SQL*Plus db file sequential read  file#   5   0000000000000005    block#  1701088 000000000019F4E0    blocks  1   0000000000000001    1740759767  8   User I/O    0   0   WAITING

And it's so annoying to see the word WAITING at the end in the column named STATE.

The other option, add a column operation takes so much time, could be possible according to the number of rows of the tables, there are more than 7 million of entries. But from now the query takes more than 3 hours.

What am I doing wrong? What's happening?


texts: MySQL 8 roles - I'm probably missing something obvious

texts:
I'm trying to use roles in MySQL 8, and failing. I have read the documentation and as far as I can tell, I'm doing it right. Since this is a 'hello world' level attempt, I assume I'm missing something very obvious, but I can't figure it out. Any help will be highly appreciated.

Here is what I'm trying to do.

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'reader_password';

CREATE ROLE 'read_all';

GRANT SELECT ON MySchema.* TO 'read_all';

GRANT 'read_all' TO 'reader'@'localhost';

The above script is executed with root user, and all statements succeed.

Then, I open a new connection using the 'reader' user credentials, and connect successfully. However, any attempt to SELECT from any table in MySchema is denied with the error message:

SELECT * FROM MySchema.SomeTable;

SQL Error [1142] [42000]: SELECT command denied to user 'reader'@'localhost' for table 'SomeTable'

I expect this SELECT to succeed as the role has SELECT permissions, and the reader user has been granted the role. What am I missing?

Have a wonderful weekend!

UPDATE: Following Rick James' comment, here are the relevant SHOWs:

On the root connection (with active schema context 'MySchema'):

SHOW CREATE USER 'reader'@'localhost';
--------------------------------------
CREATE USER 'reader'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*96B291A4F8FC2B6C453E4C54AD2080751B0D4712' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT

SHOW CREATE USER 'read_all';
----------------------------
CREATE USER 'read_all'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

SHOW GRANTS FOR 'reader'@'localhost';
-------------------------------------
GRANT USAGE ON *.* TO `reader`@`localhost`
GRANT `read_all`@`%` TO `reader`@`localhost`

SHOW GRANTS FOR 'read_all';
----------------------------
GRANT USAGE ON *.* TO `read_all`@`%`
GRANT SELECT ON `MySchema`.* TO `read_all`@`%

And on the reader connection:

SHOW GRANTS;
------------
GRANT USAGE ON *.* TO `reader`@`localhost`
GRANT `read_all`@`%` TO `reader`@`localhost`


texts: Dependent Transaction in separate Database-Connections

texts:
I am quite new to database and I am in the following situation.

I have two tables defining a 1:n relation, let's say Company and Employee which I access in two separated database connections in postgres.

I am now writing a new company and some of its employees to the respective tables, in two separate transactions.
I then commit the company transaction and then the employee transaction.
But what should I do if the employee transaction failed, AFAIK I can't unroll the committed company transaction?

I have the feeling that my approach of handling dependent transaction in separate Database-Connections is not from the book.


texts: Optimizing concurrent updates in Postgres

texts:
I'm running concurrent Postgres queries like this:

UPDATE foo SET bar = bar + 1 WHERE baz = 1234

Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which the rows are updated, I end up with deadlocks. Currently I fix the problem by enforcing the order by hand, but this means I have to execute many more queries than I normally would while also raising the search complexity from O(log N + K) to O(K log N).

Is there a way to improve performance without ending up vulnerable to deadlocks? I suspect that replacing the (baz) index with the (baz, id) index might work provided that Postgres updates the rows in the same order that it have scanned them, is this an approach worth pursuing?


texts:
I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more then 80 different indexes. Besides that I suspect the database server has a lack of memory.
I know that many indexes can harm performance of UPDATE , INSERT AND DELETE actions.
I wonder if so many indexes might decrease the performance of complicated SELECT query?


texts: Load index to cache is not using memory available in the key_buffer_size variable

texts:
Good afternoon folks,
I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading even though there are still indexes to be loaded.
Has anyone had this problem or know what I can do to solve it?
The server that is running the database is a windows server 2012 (¯_(ツ)_/¯ - and don't want to switch to linux server), MySQL is version 5.5.62 and in this instance I have 1210 databases with the structure of the tables in MyISAM.
Below is the configuration file (my.ini):

  [mysqld]
   port=3306
   basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
   datadir="D:/MySQL/Data/"
   character-set-server=latin1
   default-storage-engine=myisam
   sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
   max_connections=4500
   query_cache_size=48M
   query_cache_type=1
   tmp_table_size = 1G
   max_heap_table_size = 256M
   thread_cache_size=1300

   key_buffer_size=62G
   read_buffer_size = 200M
   read_rnd_buffer_size = 100M
   sort_buffer_size = 1G
   myisam_sort_buffer_size = 4096M

  skip-innodb
  max_allowed_packet=16M
  interactive_timeout=300
  wait_timeout=1700
  max_sort_length=4096
  max_connect_errors=1000000
  table_open_cache=13999
  table_definition_cache=4096
  log-warnings
  long_query_time=10
  open_files_limit=65535
  slow-query-log
  slow_query_log_file = "D:/MySQL/Data/slowlog.txt"
  log_output = TABLE
  connect_timeout=30
  net_read_timeout=65
  net_write_timeout=65
  tmpdir="D:/MySQL/Temp/"

Since there is still a lot of memory available for the cache and most of the scripts are data queries


texts:
I have (more than one instance) some of my experiments with SQL Server 2008 eval (why eval --> brutal bureaucracy about using licenced version for testing or short life-cycle experiments), and after expiration I saved image of virtual, and now I want to start up some of them by using DataCenter licence, but this version doesn't allow me to reinstall or upgrade expired instance with options to hold system setting, users, linked servers or etc....

my questions:

Is there some version of SQL Server 2008 (ms-whatever didn't gave me an answer) that be able to start up expired eval?
Is there comment about uninstall and attach from File?
(maybe really stupid) Is it possible to un-install eval and install Datacenter by using some parameters from command line, or some 3rd. party tools for that?


texts: Why does PostgreSQL FDW talk to multiple backend servers for this point query?

texts:
I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers:

CREATE TABLE my_big_table
(
    user_id bigint NOT NULL,
    serial bigint NOT NULL,         -- external, incrementing only
    some_object_id bigint NOT NULL,
    timestamp_ns bigint NOT NULL,
    object_type smallint NOT NULL,
    other_type smallint NOT NULL,
    data bytea
) PARTITION BY LIST (mod(user_id, 4)) ;

CREATE SERVER shardA
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.11', port '5432', dbname 'postgres', fetch_size '10000');
 .
 .
 .
CREATE SERVER shardD
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.14', port '5432', dbname 'postgres', fetch_size '10000');

create foreign table my_big_table_mod4_s0 partition of my_big_table
    for values in (0) server shardA
    OPTIONS (table_name 'my_big_table_mod4_s0');
 .
 .
 .
create foreign table my_big_table_mod4_s3 partition of my_big_table
    for values in (3) server shardD
    OPTIONS (table_name 'my_big_table_mod4_s3');

Given a query for a single user_id, I was hoping for FDW to select a single backend based on simple partition pruning, but explain shows a foreign table scan against all four servers... How can I hint FDW to be smarter?


texts: Best way(s) to time ad hoc queries and stored procedures

texts:
What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures?

Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?


texts: Why you want to avoid Dynamic SQL in stored procedure?

texts: I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's flexibility. My guess is about SQL Injection or Performance. Anything else?


texts: Adding specific information to a local file

texts:
I have several files on a webserver (not stored in the database). I'd like to store several information to this specific file. The user should be able to post comments about this file.

My question is:

What is the best way in order to store information in a database in addition to a file that is stored on a webserver... How do I connect both parts?


texts: Get all orders that are "ordered" but not "paid"

texts:
Let's say I have two tables.  Order and OrderState.

Order
ID|Description|
1 | tv order
2 | computer order

OrderState
ID|Description|OrderID
1 |ordered    | 1
2 |paid       | 1
3 |received   | 1
4 |ordered    | 2

How would one get all orders that are "ordered" but not "paid"?

SELECT * FROM Order as o
    INNER JOIN OrderState as os
        on o.ID = os.OrderID
WHERE os.Description='Ordered' AND NOT EXISTS(
    SELECT * FROM Order as o
        INNER JOIN OrderState as os
            on o.ID = os.OrderID
    WHERE os.Description='paid');

I tried the query above but it does not seem to work.


texts: How to use TO_NUMBER function to update columns of a table Using oracle pl/sql

texts:
Hope you're doing well.
I have a table with this structure :

Table1(Cust_No    Varchar2(50),
       First_Name Varchar2(50),
       Last_Name  Varchar2(50),
       Error_Code number(1),
       Error_Desc Varchar2(50))

At first , data will be inserted into column Cust_No and I should
Update other columns of the table based on this column.The point is that all cust_No must have number format. For example Cust_No ='aa674' does not have a correct format.
I need to write a query to update the table and set
column First_Name='--' , columnLast_Name = '--',
column Error_Code=1 and column Error_Desc='Incorrect format'
for those Cust_No which has incorrect formats.
I wanted to use TO_NUMBER() function but it gives me invalid number error.
how may I solve this problem?
Thanks in advance


texts:
I have a date formatted as follows:

00:50:34 Jun 25, 2017 PDT

.. actually I have it in this format

00%3A50%3A34+Jun+25%2C+2017+PDT

but can easily transform in the above.

I was wondering if there's a quick and simple way to transform it in a date I can use in a datediff function like

set nsDiff = datediff(ns,thisstrangedateformat,sysdatetime())


texts: Do we need wal_keep_segments when we have archive_command?

texts:
I'm trying to clarify some concepts about how archive_command in conjunction with wal_keep_segments

Please correct me if I'm wrong.

wal_keep_segments: if slaves fall behind wal_keep_segments then downstream connections will fail and we have to recovery all downsteam connections (slaves)

archive_command: if slaves lag behind wal_keep_segments (excess) then pg start to purge oldest segment in pg_wall/to archive/

If two above statements are correct, does pg always wait for wal_keep_segments excess before doing purge segment to archive_command? that mean downstreams need to always failed.

in recovery.conf, I have:

restore_command = 'cp /var/lib/postgresql/11/main/archive/%f %p'

does that mean I don't need to set wal_keep_segments if I've set archive_command?

Edit: I need to clarify some questions:

does file under archive/ folder auto rotate by pg? or we need crontab to clean it up?
in case of disaster, we can manually recovery from archive/, I wonder does we really need to set wal_keep_segments to non-zero number? (or keep it default to zero so all segments will be put in archive?
if we set both wal_keep_segments and archive_command, does pg wait till segments excess wal_keep_segments before purging to archive/ or does it purge immediately?


texts: MySQL and MariaDB: COLLATE on table vs COLLATE on all columns?

texts:
Will the following table behave or perform any differently

CREATE TABLE t1 (
  foo char(60) COLLATE utf8_bin,
  bar char(64) COLLATE utf8_bin,
  baz char(80) COLLATE utf8_bin
) DEFAULT CHARSET=utf8;

than this table

CREATE TABLE t2 (
  foo char(60),
  bar char(64),
  baz char(80)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I ask this because when I run SHOW CREATE TABLE mysql.db it shows up with the marker on every column (as in t1), and on the table (as in t2). I don't ever add DEFAULT COLLATE when I create tables. I'm wondering why the system tables have this option set.


texts: Insert into linked server fails with "The transaction manager has disabled its support for remote/network transactions"

texts:
Two servers: sq009 and sq202, both running SQL Server 2012 SP3.

sq202 is the SQL Server hosting the DB for our ERP system, Sage 500.
sq009 is the SQL Server hosting the DB for our IBM Maximo instance.

The situation: a trigger exists on a table in sq202, which should insert a row in the sq009 DB when a row is inserted in the sq202 DB. However, I get this error in Sage 500 dialog when the record is inserted:

  The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "sq009" was unable to begin a distributed transaction.
  OLE DB provider "SQLNCLI11" for linked server "sq009" returned message "The transaction manager has disabled its support for remote/network transactions.".

Both servers have the other as a Linked Server; both have all connections being made using a specified security context; both have the server option Enable Promotion of Distributed Transactions for RPC set to False.

I have confirmed the Distributed Transaction Coordinator service is running on sq009, and set its permissions appropriately I believe (even though I understand this shouldn't be needed):

but sq009 still is apparently complaining.


texts:
When browsing my cube it shows the correct invoice values by date when I select only a date type (the date table was generated by SSAS with hierarchies), but if I add the customer name it shows the combined value of the invoices for the date period, rather than the total per customer. I expect to see the total value for the customer that month, not the overall monthly total.

My tables are joined with foreign keys and the DSV shows a star schema. A simplified version of the schema is below.

factInvoice
invoicePK
customerFK -> customerPK
dateFK -> datePK

dimDate
datePK

dimCustomer
customerPK

The dimension usage tab shows both dimensions are linked to the invoice table.

Please can somebody shed some light on why this is happening? I'm sure I'm missing something pretty basic here! Thanks.


texts: Im trying to group two queries by year so that it will show the amount of tours done(by count of tour id) in that year

texts:
SELECT [school tours done showing year].[CountOfTour ID],
       [Visitor Tours Done Showing Year].[CountOfTour ID],
       Year([Date]) AS [Year]
FROM   [school tours done showing year], [Visitor Tours Done Showing Year];


texts: MySQL adds broken comment script after each line

texts:
Every time I try to create a table using MySQL Workbench 6.3 CE it adds "broken" comments at the end of the script.

Example:

I am trying to create a table called "user". This is what the table should look like:

However when I click the apply button, the script generates incorrectly:

CREATE TABLE `user` (
  `userid` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `record_status` INT NOT NULL DEFAULT 1 COMMENT '',
  `createdby` VARCHAR(75) NOT NULL COMMENT '',
  `createddate` DATETIME NOT NULL COMMENT '',
  `updatedby` VARCHAR(75) NULL COMMENT '',
  `updateddate` DATETIME NULL COMMENT '',
  `username` INT NOT NULL COMMENT '',
  `password` VARCHAR(75) NULL COMMENT '',
  `firstname` VARCHAR(75) NULL COMMENT '',
  `lastname` VARCHAR(75) NULL COMMENT '',
  `email` VARCHAR(75) NULL COMMENT '',
  `facebook_userid` VARCHAR(75) NULL COMMENT '',
  `email_verified` VARCHAR(75) NULL COMMENT '',
  PRIMARY KEY (`userid`, `username`)  COMMENT '');

The comment in this line should not be there, so the code should be:

PRIMARY KEY (`userid`, `username`));

and the code executes when I paste it into an editor and remove this comment. However I create massive databases daily. (Each with +- 15 tables). I can't keep doing this for each table as it is time consuming.

How can I disable this feature that adds the comments to the script?
I don't need them in my databases


texts: Postgresql sudden storage increase at regular interval?

texts:
I am running Postgresql 11 in production and observing a weird step function kind of thing in storage as follow:

The above graph is for a single day and it is happening everyday at random times. I have checked and there are no bulk inserts from our side. Also, these steps are weird because storage again comes back to its normal value after some time.
I also have read replica and because of this, there is sudden increase in disk usage of read replica which results in high replication times of around 30s, which in further results in conflict with recovery problem. Is there a way to deal with this? Is this common behaviour of Postgres. I am running postgresql on Google Cloud SQL and it has been happening for a long time but I observed today, so I am wondering is it a normal thing or do I need to check something?
One suspicion I have is that it might be because of vaccumm but I am not sure? Please help me with this.
Edit 1:
There are spikes like below whenever this occur for read/write io. Red is the write one and blue is the read one


texts: Convert columns into rows for use with indexed/materalized view

texts:
I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. first name, middle name, last name, home number).

I've had success doing so with UNPIVOT and CROSS APPLY, respectively. However, I was hoping to improve performance for a large set of records (>500k) and all in one query. The table has a primary clustered index; however, according to the execution plan, for UNPIVOT or CROSS APPLY, both are performing a Nested Loop (Left Outer Join), at a 31% cost. Again, it's not like the query is agonizingly slow, but I'd like to inch out every bit of performance I can achieve.

My thought process was, OK, maybe if I can create an indexed view, the query will run very fast.

The problem is SQL Server indexed views (materialized views in Oracle-speak) have so many restrictions such as UNPIVOT and CROSS APPLY (see the full list here). Since CROSS APPLY is effectively an INNER JOIN and joins are acceptable for indexed views, maybe that's at least one good alternative. Even so, I'm having some difficulty rewriting CROSS APPLY as an INNER JOIN.

Here is the table (I'm showing 6 records to simplify but imagine it's 500K or more records):

UserAuditTbl

RowID     UserID      UserAttribute     OldValue     NewValue
1         ID00184     First Name        John         Jon
2         ID00184     Last Name         NULL         Albert
3         ID00185     Home Phone        555-555-1122 555-555-1212
4         ID00188     Middle Name       Jesse        James
5         ID00188     Cell Phone        555-555-1234 555-555-1555
6         ID19594     Zip Code          00000        90210

With CROSS APPLY, the query would look like:

SELECT
   RowID
  ,UserID
  ,Column
  ,Value
FROM
  UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), 'NewValue', NewValue)
) x (Column, Value)
WHERE
  Value IS NOT NULL

I guess the first question is can I convert columns into rows such that the underlying query can be incorporated into an indexed view? And the second question is if it's best just to avoid indexed views, then what are my options here? ETL to a new table via SSIS or trigger?

Thank you for your time.


texts: postgresql materialized views vs versioning

texts:
I came across a pickle when dealing with question from one of our dev concerning postgresql and materialized views:

can I refresh materialized view with my application user?

well according to docs (https://www.postgresql.org/docs/11/sql-creatematerializedview.html), it is simple enough, BUT you must be owner of said view.

We have isolated acls for roles we run migrations under (ddl) and roles that are "application" and thus can operate with limited privileges. So I can't create the view during migration, cause application need to update it once a while and I can't create it in application runtime, because application role doesn't have create privilege.

I think someone must have came across similar issue, but search engines are not helping at all. last hope is making application user a little bit more privileged (with create on schema granted).


texts: Can MySQL Enterprise Backup perform partial backup/restore of views?

texts:
I'm not able to backup and restore views using partial backup/restore (with tts) using MEB 3.12 with MySQL 5.6.28. Is this by design?

Have been searching for quite a while but not able to find helpful information on how views, triggers and stored functions are handled by MEB. Any information on the above subject is greatly appreciated. Thanks.


texts: How can I safely kill this spid that is running on the master database?

texts:
I have a secondary logshipping database that is blocked by a pending restore. There is no process (spid) on this database itself, but I found a spid related to this database, that runs on the master database.

The spid in sp_who2

-   SPID=120
-   Status=SUSPENDED
-   Login=ABC\sqlagentuser
-   HostName=XYZ
-   BlkBy=  .
-   DBName=master
-   Command=RESTORE LOG
-   CPUTime=2153
-   DiskIO=176
-   LastBatch=09/21 00:46:02
-   Program Name= SQL Server Log Shipping
-   REQUESTID=0

More info with another query on sysprocesses and sysdatabases

-   Login_time=2019-09-21 00:46:01.857
-   Memusage=3
-   Lastwaittype= REQUEST_DISPENSER_PAUSE

What can I do to get safely rid of this proces? You can see that the master database is involved, that is why I hesitate on killing the spid or restarting any services (or rebooting).

Can I safely kill it? Will there be any long-time hindrance for other databases on the same instance while doing an eventual rollback? It is already suspended for a couple of days, but it is only waiting (no query activity)
Or is stopping sqlagent the thing to do? (the process is run by the user that runs the sqlagent-service) – will this kill the spid?
Or do I have to stop and restart the sql server service (“program name” is sql server log shipping) – any longtime rollback risk during stopping (that will block other databases for that long time)?


texts: Function to compute difference between 2 times

texts:
I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I have commented out seconds, just working on minutes.
CREATE OR REPLACE FUNCTION time_diff(hr INT, min INT, sec INT, exp_hr INT)
RETURNS INT
LANGUAGE plpgsql AS
$$
DECLARE
    cur_time    TIME;
    expire_time TIME;

    diff_interval INTERVAL;
    diff INT = 0;
BEGIN
    cur_time    = CONCAT(hr,  ':',  min, ':', sec) AS TIME; -- cast hour, minutes and seconds to TIME
    expire_time = CONCAT(exp_hr, ':00:00') AS TIME;         -- cast expire hour to TIME

    -- MINUS operator for TIME returns interval 'HH:MI:SS;
    diff_interval = expire_time - cur_time;

    diff = DATE_PART('hour', diff_interval);

    diff = diff * 60 + DATE_PART('minute', diff_interval);

    --diff = diff * 60 + DATE_PART('second', diff_interval);

    RETURN diff;
END;
$$;

Example:
01:15:00 - 02:00:00 should give me 45 minutes, so I do the following and I get the correct answer.
select * from time_diff(1, 15, 0, 2);

However, if I do this: 23:15:00 - 01:00:00 - the should give me 105 minutes (60 + 45).
select * from time_diff(23, 15, 0, 1);

But the result I am getting is -1335. I am trying to work out where I have gone wrong here.
Also I am invoking DATE_PART functions, this seems to be a quite an expensive process in terms of CPU usage. Is there a better way of optimising this function. With the first example I am getting results in 0.007s on 2018 i7 Mac mini. Although I do think this function is quick, but could it be better?


texts:
I'm enabling slow log query for MySQL but I notice that CREATE, INSERT, UPDATE, et. al are also included.
I'm generally not interested in those.
Is there any way to specify that only slow SELECTs are to be logged?


texts: SQL Server: Using 4 part identifiers when database may be on the same server

texts:
In my client's setup, the database servers in lower environments are all on one server for cost saving purposes, but in higher environments, these databases are deployed on separate servers, and linked servers are used when queries run across the servers.

Example setup:

Dev:
  Server1: DB1, DB2
UAT:
  Server1: DB1 (with linked server for DB2)
  Server2: DB2

I am trying to maintain the databases with a single set of SQL Server Data Tools projects, using 4 part [server].[database].[schema].[object] identifiers and database reference variables to model the relationships.  This works well when the databases are not on the same server, but fails when the databases are on the same server.

My questions:

Is there a default way to refer to the local server in a 4 part identifier other than omitting the server definition?
If not, is there a way to add a linked server pointing to the local server?
If neither of the above is possible, is there another way to manage this in SQL Server?


texts: MySQL: bind-address 0.0.0.0 in my.cnf does not work?

texts:
I'm new to mysql.
I want to connect to mysql server using whatever ip I want.
I read that all I have to do is to add to my.cnf file the line bind-address = 0.0.0.0.
That's what I did.
I restarted mysql server and then I tested it from command line.

mysql -uroot -p'*password*' -h 127.0.0.1 --> Works
mysql -uroot -p'*password*' -h 192.168.2.4 (local ip address) --->
ERROR 1045 (28000): Access denied for user 'root'@'mguru.lnx.gr' (using password: YES)

You can see below part of the section mysqld in my.cnf file

[mysqld]
user = mysql
port=3306
socket          = /opt/lampp/var/mysql/mysql.sock
skip-external-locking
key_buffer = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

plugin_dir = /opt/lampp/lib/mysql/plugin/
#skip-networking
bind-address=0.0.0.0

Thank you in advance,
Nikos


texts: Efficiently search for a prefix range in SQL Server

texts:
Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters A010 and A025, I want to return A0101, A0200 and A0259.

What I've tried:

Obviously, I cannot use WHERE myText BETWEEN @from and @to, because that won't return A0259.
Technically, WHERE LEFT(myText, @len) BETWEEN @from AND @to, would be exactly what I want, but that kills SARGability.
I could use WHERE myText BETWEEN @from and @to + 'zzzzzzzzzzz', but that is an ugly hack and potentially error-prone. (Is z really the highest character? Did I use enough "padding" characters?)
I could "explode" the range and search for every possible prefix, e.g. WHERE (myText LIKE 'A01%' OR myText LIKE 'A020%' OR myText LIKE 'A021%' ...), but that's a lot of work.

Is there some smart solution that I've missed? I'll probably use option 3 to solve the problem (since I know about the length and the range of allowed characters), but I'm curious about the general case.


texts:
This morning my slave stopped working..

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: No

  Last_SQL_Error: Could not execute Update_rows event on table
  dynaccount.account; Can't find record in 'account', Error_code: 1032;
  handler error HA_ERR_KEY_NOT_FOUND; the event's master log
  mysql-bin.000025, end_log_pos 802331975

Restoring slave

- Master
# mysqldump -u root -pxxx --single-transaction --hex-blob --routines --triggers --events --quick --add-drop-database --extended-insert --databases my_db | gzip > /root/dump.sql.gz
# scp -o 'StrictHostKeyChecking no' /root/dump.sql.gz root@remote.com:/root

- Slave
# mysql -u root_slave -pxxx -e "stop slave; reset master"

# pv /root/dump.sql.gz | gunzip | mysql -u root_slave -pxxx
# mysql -u root_slave -pxxx -e "start slave"
# mysql -u root_slave -pxxx -e "show slave status\G"

After restoring the slave I keep getting the same error?!


texts: Audit Plugin on Ubuntu: CONTROL [main] Failed global initialization: FileNotOpen: Failed to open "/path" : exit status 1

texts:
We use MongoDB 3.2.11 and try to enable Audit.

  New in version 2.6.

  MongoDB Enterprise includes an auditing capability for mongod and
  mongos instances. The auditing facility allows administrators and
  users to track system activity for deployments with multiple users and
  applications.

This is our startup configuration mongodb.conf

auditLog:
  destination: file
  filter: '{ atype: { $in: [ "createCollection", "dropCollection", "createDatabase",
    "createIndex", "renameCollection", "dropDatabase", "dropIndex", "createUser",
    "dropUser", "dropAllUsersFromDatabase", "updateUser", "grantRolesToUser", "revokeRolesFromUser",
    "createRole", "updateRole", "dropRole", "dropAllRolesFromDatabase", "grantRolesToRole",
    "revokeRolesFromRole", "grantPrivilegesToRole", "revokePrivilegesFromRole", "enableSharding",
    "shardCollection", "addShard", "removeShard", "shutdown", "applicationMessage",
    "authCheck" ] } }'
  format: BSON
  path: /opt/mongouser/store/mongodb-audit
net:
  port: 27000
operationProfiling: {}
processManagement:
  fork: "true"
replication:
  replSetName: rs-testname
security:
  authorization: enabled
  keyFile: /opt/mongouser/store/auto-mms/keyfile
storage:
  dbPath: /opt/mongouser/store/mongodb-data/rs-testname_1
  directoryPerDB: true
  engine: wiredTiger
  wiredTiger:
    engineConfig:
      directoryForIndexes: true
systemLog:
  destination: file
  path: /opt/mongouser/store/mongodb-data/rs-testname_1/mongodb.log

mongodb.log (the only entry). Here the strace output with startup.

2017-02-23T16:20:54.430+0000 F CONTROL  [main] Failed global initialization: FileNotOpen: Failed to open "/opt/mongouser/store/mongodb-audit/"
 : exit status 1

Output (stdout/stderr):

about to fork child process, waiting until server is ready for connections.
forked process: 56468
ERROR: child process failed, exited with error number 1

directory perms. The data dir and audit dir have the same permissions. The data dir works.

 # ls -ald mongodb-*
drwxrwxrwx 2 mongouser mongouser   17 Feb 23 16:19 mongodb-audit
drwxrwxrwx 3 mongouser mongouser   37 Dec  7 11:14 mongodb-data
drwxr-xr-x 6 mongouser mongouser 4096 Dec  7 11:24 auto-mms

I added maximum verbosity and there is still only one line in mongodb.log

systemLog:
  destination: file
  path: /path/mongodb.log
  verbosity: 5

about the Linux box. With a search engine you find similar errors only for Windows based Systems.

$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.5 LTS"

any idea what's wrong?


texts: Why does this query, missing a FROM clause, not error out?

texts:
So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!?

SELECT

    1
   ,r.id
   ,'0D4133BE-C1B5-4141-AFAD-B171A2CCCE56'
   ,GETDATE()
   ,1
   ,'Y'
   ,'N'
   ,oldItem.can_view
   ,oldItem.can_update

FROM Role r

JOIN RoleObject oldReport
    ON r.customer_id = oldReport.customer_id

JOIN RoleItem oldItem
    ON oldReport.id = oldItem.role_object_id
        AND r.id = oldItem.role_id

WHERE r.id NOT IN (SELECT
        role_id
    WHERE role_object_id = '0D4133BE-C1B5-4141-AFAD-B171A2CCCE56')

AND oldReport.id = '169BA22F-1614-4EBA-AF45-18E333C54C6C'


texts:
I created two multi-column indexes on a big table (~2,5 millions rows 370Mb and 621Mb with indexes).

SELECT queries are very fast.

Know I want to check if we have a regression for INSERT/UPDATE queries.

Is there any script to perform EXPLAIN ANALYZE INSERT/UPDATE without write anything in the table (with BEGIN, ROLLBACK statements)?

I want to run the script without indexes and with indexes to compare execution times.

I am using Postgresql v9.5


texts: Running a query on an improperly indexed, 600gb table

texts:
This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data.

Originally this data was supposed to come in for one state, but data from the entire country has been storing here for a long period of time.

I have been put in charge of running a query to isolate a LATITUDE/LONGITUDE bounding box in this momentous data set within a certain date range.

To make things even worse, the database is located on an old server which runs on a quad p3 processor with only about 4gb of ram so it's incredibly slow.

Query I would like to execute, but for obvious reasons cringe just looking at:

INSERT INTO gpsdata.vehicle_location_wa SELECT * FROM gpsdata.vehicle_location
WHERE LOCATION_TIMESTAMP BETWEEN '2014-03-20' AND '2014-07-20'
AND LATITUDE BETWEEN 46.86275036 AND 47.80149551
AND LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

Table structure:

CREATE TABLE `vehicle_location` (
`SEQ_ID` decimal(10 , 0 ) default NULL,
`DEVICE_ID` varchar(100) collate utf8_unicode_ci default NULL,
`DATA_TYPE` varchar(80) collate utf8_unicode_ci default NULL,
`DATA_DESC` varchar(180) collate utf8_unicode_ci default NULL,
`SPEED` decimal(6 , 3 ) default '0.000',
`SPEED_UOM` char(3) collate utf8_unicode_ci default NULL,
`DIRECTION` decimal(3 , 0 ) default NULL,
`DURATION` decimal(10 , 0 ) default '0',
`LATITUDE` decimal(16 , 13 ) default NULL,
`LONGITUDE` decimal(16 , 13 ) default NULL,
`GPS_STATUS` tinyint(4) default NULL,
`LOCATION_TIMESTAMP` datetime default NULL,
`TIMEZONE` tinyint(1) default NULL,
`DST` tinyint(1) default NULL,
`STATUS` tinyint(1) default NULL,
`MILEAGE` decimal(15 , 2 ) default '0.00',
`QUEUE_TIME` datetime default NULL,
`OBSERVATION_TIME` datetime default NULL,
`ID` BIGINT NOT NULL auto_increment,
PRIMARY KEY (`ID`),
KEY `DEVICE_ID` (`DEVICE_ID`),
KEY `Location_Timestamp` (`LOCATION_TIMESTAMP`),
KEY `Data_Type` (`DATA_TYPE`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_unicode_ci

Some things I thought about doing:

Selecting only by date range into a new table with two extra indexes: LATITUDE and LONGITUDE [Seems like best option]
Moving database onto a much faster machine with loads of ram (This has its own issues, the whole database is 3.5 tb of data on a raid)
Adding an index to table (gasp)

I'm hoping someone with a bit more experience can help guide me in the right direction or affirm some of my thinking before I start executing queries that could take days to process.

Thank you for your help!


texts: Is there a way to automatically upgrade DBeaver?

texts:
I am trying to update DBeaver Community Edition 7.0.3 to (currently) newest 7.1.0 edition on Fedora 30. When I start DBeaver a "Version Update" window pops up with buttons as options to Upgrade and Details (manually download installation file). When I click on Upgrade it freezes window and program crashes.

Is there any way to upgrade it AUTOMATICALLY without loosing any setup (DB connections, themes, etc.) or package MUST be manually replaced?


texts: Reverse connect by prior level value for arbitrarily-deep hierarchy

texts:
Background

Using a menu hierarchy to drive a login process for users. Users have the ability to set their preferred menu item. When they log in, if they have a preferred menu item set, the system directs them to that item. If no preferred menu item is set, they log into the default menu item for their "most important" role.

Code

The query uses connect by prior to get the list of menus:

  SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'USERNAME' AND
        ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id

Problem

A root menu item has NULL for its parent (mim_id). The user's menu preference is a menu item leaf node, which can be found at any level in the hierarchy (the maximum depth is 3, in this case).

When the data is returned, the values for the LEVEL pseudocolumn (alias MENU_LEVEL) are in reverse order:

╔════════════╦═══════════╦══════════════╗
║ MENU_LEVEL ║ MENU_NAME ║ MENU_ITEM_ID ║
╠════════════╬═══════════╬══════════════╣
║          1 ║ MenuTab3  ║ 100436       ║
║          2 ║ MenuTab2  ║ 101322       ║
║          3 ║ MenuTab1  ║ 101115       ║
╚════════════╩═══════════╩══════════════╝

This should actually return:

╔════════════╦═══════════╦══════════════╗
║ MENU_LEVEL ║ MENU_NAME ║ MENU_ITEM_ID ║
╠════════════╬═══════════╬══════════════╣
║          3 ║ MenuTab3  ║ 100436       ║
║          2 ║ MenuTab2  ║ 101322       ║
║          1 ║ MenuTab1  ║ 101115       ║
╚════════════╩═══════════╩══════════════╝

However, since the hierarchy is connected by starting from the user's preferred menu item, and worked back up to the root menu item, it makes sense that LEVEL is counting "backwards".

Having the level reversed means we can ask, "What is the 3rd-level menu item for the user named 'USERNAME'"? Expressed in as a SQL where clause:

WHERE menu_level = 3 AND username = 'USERNAME';

Question

How would you reverse the value of LEVEL for an arbitrarily-deep hierarchy?

For example, something like:

SELECT
  LEVEL AS MENU_LEVEL_UNUSED,
  max(LEVEL) - LEVEL + 1 AS MENU_LEVEL
FROM ...

Obviously that won't work because max is an aggregate function.

Fiddle

http://sqlfiddle.com/#!4/60678/3/0

Strangely, I'm seeing different behaviours in Fiddle's 11g R2 instance than the local Oracle instance -- the ROWNUM is picking up "1" in the Fiddle when it should be picking up "3". This prevents seeing the menu hierarchy, and hence the LEVEL. Not sure why.

Ideas

We could add a column to jhs_menu_items that stores the depth. This is a bit redundant, though, because the hierarchy itself contains that information.
We could wrap the jhs_menu_items table in a view that calculates the depth. This could get computationally expensive.
Is this a good candidate for WITH?


texts: Is there any way to change the size of preallocate data file in mongodb?

texts: In mongodb data files are preallocates to a particular size,like first data file .0, the next .1, etc. The first file mongod allocates is 64 megabytes, the next 128 megabytes, and so on, up to 2 gigabytes, at which point all subsequent files are 2 gigabytes. Is there any way the default size can be change to 2GB? if so what will be the impact on the performance.


texts: Row level security with a single DB user and connection pooling

texts:
I'm using node-postgres to connect to a PostgreSQL 9.6 database with connection pooling enabled. All connections user the same database user. One reason I can't use multiple DB users is that as far as I read, row level security and views don't work well together in that case as the owner of the view is used for RLS.

I'm now looking at using row level security in Postgres, and I'd like to make sure I'm doing this correctly.

I'm using SET LOCAL to set the current application user id which is then used by the row level security USING clause. The only way I can think of achieving this with connection pooling is to wrap every query in a transaction with node-postgres and execute the SET LOCAL command like the following in every transaction.

SET LOCAL postgres.my_user = 20;

The following code is a simplified example of how I want to define the row level security, the real version has a few more conditions:

CREATE POLICY table_a_read_policy ON table_a FOR SELECT
USING (
    EXISTS (
    SELECT *
    FROM permissions
    WHERE
        user_id = current_setting('postgres.my_user')::int AND
        permission_type = 'read'
    )
);

My understanding is that any SQL injection in this case would also give the attacker the ability to just set any user id and circumvent row level security this way. But I don't see any way to avoid this as the db user I connect with must have the ability to see all data for the entire application.

There are a few points I'm not entirely sure about:

Is using SET LOCAL as a way to store the current application user identity for row level security safe? Especially in the context of connection pooling?
Is there a better way than wrapping every query in a transaction in node-postgres and executing the SET LOCAL command every single time?
Is there a way to structure this (with the restriction that the database connection is made using a single database user) in a way where an SQL injection would not automatically give the ability to circumvent row level security?


texts: Time Series Table ( Many Columns ) Design Recommendations

texts:
I am storing Time Series Data in a Postgresql database.

I am wondering what the performance ramifications are of the following two proposed design patterns.

Option 1:
    A single table with a timestamp and many columns ( maybe 100 ) containing various measurement data.

TABLE measurement (id, timestamp, measurementA, measurementB, measurementC ...)

Option 2:

Two tables. One with an id and timestamp and another table which contains a reference to the measurement table and then multiple fields describing the recorded data.

TABLE measurement (id, timestamp)
TABLE measurement_data (id, measurement_id, description, type, value)

I like the idea of option two. As my table structure is much smaller and simpler. However I have concerns of the effects of queries on the table. Will there be a huge performance hit for a simple query such as the following?

Using Option 1s Design

SELECT measurementA
FROM measurement
WHERE timestamp > X AND timestamp < Y

Using Option 2s Design

SELECT value
FROM measurement_data
WHERE description = 'measurementA'
  AND measurement_id IS IN (SELECT id
                            FROM measurement
                            WHERE timestamp > X AND timestamp < Y )


texts:
Is it possible to somehow do this?

WITH T1 AS
(
   SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
   select 6 as seq, 'SOMETHING 4' AS SOME_TYPE from dual
)
, T2 AS
(
   SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
   SELECT 'B' AS COMPARE_TYPE FROM DUAL
)
SELECT T2.*, T1.*
FROM T1, T2
WHERE  CASE T2.COMPARE_TYPE
         WHEN 'A'
            THEN T1.SOME_TYPE LIKE 'NOTHING%'
         ELSE T1.SOME_TYPE NOT LIKE 'NOTHING%'
      END

I know that my WHERE is clause is not correct.

Any help would be great in knowing if this type of statement is possible.

I don't want to write a Dynamic SQL.  If I have to I will write 2 different SQL statements.

Thanks


texts:
select *
from ( select a.wc_name as Team,
              g.name as Buyer_name,
              e.style_no,
              e.item_no,
              d.receipt_date,
              sum(c.QTY)
       from workcenter a,
            job b,
            Job_Receipt_Details c,
            Grn_Receipt d,
            item e,
            "ORDER" f,
            buyer g
       where C.Job_Id=b.job_id
         and a.wc_id=b.wc_id
         and e.order_no=f.order_no
         and f.buyer_id=g.buyer_id
         and c.item_no=e.item_no
         and c.grn_no=d.grn_no
       group by a.wc_name,
                g.name,
                e.style_no,
                e.item_no,
                receipt_date )
where to_date(to_char(receipt_date,'DD-MON-YY'),'DD-MON-YY')=to_date(to_char(sysdate,'DD-MON-YY'),'DD-MON-YY')

Need hourly base separate column
Current OutPut:

Expected:


texts: Why can't we specify group functions in Oracle in the where clause?

texts:
SQL> desc tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(1000)

SQL> select * from tab1;

        ID NAME
---------- ---------------
         1 a
         2 b
         3 c

SQL> select * from tab1 where id > AVG(id);
select * from tab1 where id > AVG(id)
                              *
ERROR at line 1:
ORA-00934: group function is not allowed here

SQL>

The error is very clear and says that this cannot be done. But I don't see why. The query makes perfect sense:

  select all rows from tab1 whose id  value is greater than the average


texts: CTE vs Function/trigger performance in Postgresql

texts:
I have a very long CTE query that I have shortened here for readability. But it tries to insert state, county, city, zipcode and street into their own tables and then insert their ID for inserting the address in the property table.

with tmpstate as (
insert
    into
        state ....on
    conflict ( lower(state_code) ) do
    update
    set
        state_code = excluded.state_code returning id,
        state_code ),
tmpcounty as (
insert
    into
        ..... on
    conflict ( lower(county),
    state_id ) do
    update
    set
        county = excluded.county returning id,
        county ),
tmpcity as (
insert
    into
      .....
    values ( 'Sarasota',
    (
    select
        id
    from
        tmpcounty
    limit 1 ),
    (
    select
        id
    from
        tmpstate
    limit 1 ) ) on
    conflict ( lower(city),
    county_id,
    state_id ) do
    update
    set
        city = excluded.city returning id,
        city ),
tmpzipcode as (
...... returning id,
        zipcode ),
tmpstreet as (
  .... ),
tmpproperty as(
insert
    into
        property (full_address,
        street_address,
        street_id,
        city_id,
        state_id,
        zipcode_id,
        county_id,
        property_details )
    values('ssss',
    'ssss',
    (
    select
        id
    from
        tmpstreet
    limit 1),
    (
    select
        id
    from
        tmpcity
    limit 1),
    (
    select
        id
    from
        tmpstate
    limit 1),
    (
    select
        id
    from
        tmpzipcode
    limit 1),
    (
    select
        id
    from
        tmpcounty
    limit 1),
    '{}') returning id)
select
    id
from
    property
limit 1

Would there be any performance gain if I create functions/triggers that insert the street, city, etc... and return their id if they exist instead of this long CTE query?


texts: Set Maximum Server Memory Higher Than What is Available to OS

texts:
I have a server that is being repurposed into a SQL Server.  It came with more physical memory than the 32GB that can be seen by my OS (Windows Server 2008 R2).

Management would like me to configure the Maximum server memory used by SQL (SQL Server 2008 R2 Service Pack 3) to be higher than what is seen by the OS.  I can change it in the properties of SQL Server via SQL Server Management Studio.  Will SQL Server be able to utilize the additional RAM?  Or will it exhaust all memory made available to the OS?


texts: Mongodb find document with too large key

texts:
I'm trying to create an index and I'm getting this error:

  2018-12-30T15:28:30.853-0800 I COMMAND  [conn580067] command db.$cmd
  command: createIndexes { createIndexes: "node", indexes: [ {
  background: true, sparse: true, key: { type: 1, players: 1, ct: 1,
  image: 1, domain: 1 }, name: "browse" } ], writeConcern: {} }
  exception: WiredTigerIndex::insert: key too large to index, failing
  5813 { : "card", : null, : 1390347921368.0, :
  "/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAAkGBxISEBIUEhQVFhQQEBAQDxAVFA8UFBUQFBQWFxQRFBQYHCggGBolHBQUITEhJSkrLi4uFx8zODMsNygtLisBCgoKDg0NFBAQFywcHBwsLCwsLCwsLC...",
  : null } code:17280 numYields:3312 reslen:354 locks:{ Global: {
  acquireCount: { r: 3313, w: 3313 } }, Database: { acquireCount: { w:
  3313, W: 2 } }, Collection: { acquireCount: { w: 3313 } }, Metadata: {
  acquireCount: { W: 1 } } } protocol:op_query 8206ms

How do I figure out which document it is so that I can delete it?

mongod version: 3.4.11 (WiredTiger)


texts: Change owner of relation in PostgreSQL database

texts:
I just started using PostgreSQL and have problems in changing the ownwer of a table. I have a database postgis_in_action and a user postgis_in_action. As I worked as postgres user and as well as postgis_in_action user my tables ch01.highways and ch01.highways_staginghave different owners postgres and postgis_in_action. They are in the same schema ch01. I checked the following command in psql as postgres user in my database postgis_in_action:

ALTER TABLE ch01.highways_staging OWNER TO postgis_in_action

Do I have to add or modify something if I want to change the owner for ch01.highways_staging to postgis_in_action?

In psql I don't get any error message and the result of the command is that the ownwer remains postgres.

I'm logged in to my database with user postgres because I wanted to have the priviledge to change the owner of a table/relation.


texts: Efficient methods/tools for managing a data warehouse pulling data from multiple database applications

texts:
Until now I have used an on premise SQL Server 2012 and performed manual ETL operations to create/manage a data warehouse that contains data from multiple database application from different organizations.

I get the source data [several dozen csv/txt files some of them 50 MB in size] every month using 2 methods: 1) execute sql queries on the source databases (for the client-server applications) and 2) using a web-based reporting tool (for the web-based applications). The destination are databases in my SQL Server based data warehouse, which I manage using SSMS/SSIS. This manual process easily takes several hours each month to update the data warehouse. I now want to update the data on a more frequent basis (possibly daily) for better analytics/reporting. I suppose it would involve gaining direct access to the source database applications?

I need to get some high level information on the methods/tools out there to accomplish this and make this process efficient/automated.


texts: Why are my SQL Server 2008R2 databases not mirroring?

texts:
I'm trying to set up database mirroring in SQL Server 2008R2. I've taken a full backup and a transactional backup from my principal database and I've restored both WITH NORECOVERY.

However, my mirror database is now stuck in RECOVERING mode and when I hit "start mirroring" on my principal, it says it can't connect.

What am I doing wrong?

Edit: I should probably state that my database is rather large (the mdf file is about 4.8GB) so that could be why.

Edit2: I've also tried doing this with both firewalls turned off so I know it's not a firewall issue.

Edit3: I've run the SQL that Mark suggested. The principal results are here: http://piersonthe.net/principal.xls and mirror are here: http://piersonthe.net/mirror.xls

It's worth noting that I got the following error when I ran the query on the mirror:
Msg 927, Level 14, State 2, Line 1
Database 'RHSCMSSites' cannot be opened. It is in the middle of a restore.


texts: Beginner PL/SQL: Return row value from dynamic SQL function

texts:
I have a PL/SQL function that successfully compiles:

01    WITH FUNCTION find_CV_errors(
02        l_table_name IN VARCHAR
03        ,l_field_name IN VARCHAR
04        ,l_domain_name IN VARCHAR
05        )
06        RETURN VARCHAR
07        AS
08        l_return_value VARCHAR(128);
09    BEGIN
10
11    EXECUTE IMMEDIATE
12        'SELECT
13            LISTAGG(CAST(' || l_field_name || ' AS VARCHAR(50)),  ' ||  ''';  ''' || ') WITHIN GROUP (ORDER BY NULL)
14        FROM ' ||
15            l_table_name ||
16        ' LEFT JOIN
17            (
18            SELECT CODE
19            FROM  ENG.D_CV_ENG_VW
20            WHERE DOMAIN = :bv1
21            )
22        ON ' || l_field_name || '  = code
23        WHERE ' ||
24            l_field_name || ' IS NOT NULL
25            AND code IS NULL'
26    INTO l_return_value
27    USING l_domain_name;
28    RETURN  l_return_value;
29    END;

The function has a dynamic sql query that outputs a single column in a single row via LISTAGG.

I want the function to return the the dynamic sql output as it's return value. However, right now it just returns NULL, because I haven't assigned a value to the l_return_value variable.

How can I assign the output from the dynamic SQL query to the l_return_value variable?

Update #1:

I've added INTO l_return_value; to the query (LINE 26), but now I'm getting an error:

ORA-06553:PLS-103: Encountered the symbol "L_FIELD_NAME" when expecting one of the following:  : = , ( @ % ;

Update #2:

When I remove the semicolon from the end of line 26, the function returns the value of l_field_name, not the value from the dynamic sql query output.

Update #3:

In addition to the errors Balazs Papp pointed out, I had a bind variable on line 13, when really I should have used a concatenated variable. Since Balazs Papp's answer uses generic examples, and not a corrected version of the question, I've corrected the original question myself (lines 07, 13, 26, 27, 28).


texts: Created database does not appear in database list

texts:
I created a new PostgreSQL 9.5 database as follows:

[root@myhost pgsql]# sudo -u postgres initdb -D /var/lib/pgsql/test
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_CA.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/lib/pgsql/test ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/test -l logfile start

If I then ask psql to list the databases, but my 'test' database is missing:

[root@myhost pgsql]# sudo -u postgres psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 |
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Can someone explain what's wrong?  Do I have to register my new test database with PostgreSQL somehow?

I also tried to backup the new database but that too fails:

sudo -u postgres psql -d /var/lib/pgsql/test -c "SELECT pg_start_backup('test');"
psql: FATAL:  database "/var/lib/pgsql/test" does not exist


texts: The specs say "limited to the lesser of 1 socket or 4 cores" however if you have two express instances on the same server and that server has 8 cores CPU 0-7, is it possible to assign instance 1 to CPU 0-3 and instance 2 to CPU 4-7?


texts: How to change temporary directory for pg_dump?

texts:
I'm running out of space from my hard drive which has a 18GB PostgreSQL database.
When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run out of space and the dump fails.

I tried this from Stackoverflow and a small file is created in the new directory, but nothing else, and pg_dump still writes to the original disk.

How do I change temp directory for pg_dump?

Note: My work_mem setting is pretty high already, I can't change that.
My db version is 9.0.13.


texts: Ports to use for mongodb sharding + replication

texts:
When deploying a replica set or a sharded cluster (any situation with several mongod's really) - how does one know what port numbers to use?

Up until now I have used all the port no's I've found in examples and tutorials, but there doesnt seem to be any obvious system or way to go about it.
For example, one example uses ports 27010 - 27014. Another example uses 27001-27003, 27020-22, 26050 etc.

The only information I found on the docs of mongodb, was the one of the default ports.

As a student (so not production deployment) who is just using a single machine, what should I use? The default ones are only 4 and I probably need atleast 8 mongod's running (for a small sharded cluster with replica sets).
Can more than 1 shardserver be using the same port?

(I am sure this is a naive question as this has not been asked about other places nor addressed in any of the mongodb books I have found, but I want to know.)

Thankful in advance.


texts: How to build the database for subdomain multisite?

texts:
I'm building a kind of "multi site application" based on wildcard subdomain. And every site will of course have the same files but also the same database structure. But it's not necessary for each and other to share tables. They will never use each other's datas.

So the best would be if they had their own tables, in their own databases. But then it's more difficult to develop the service since I have to do all the changes to all the databases when I change something.

So the best would be if every database could mirror each other in the terms of structure.

Is this possible? Or what is the most recommended solution for this situation?


texts: How do I abstract a WHERE clause condition?

texts:
I'm wondering how a condition in a commonly used WHERE clause can be abstracted away so that it can be safely changed.

Say I have data like

ColA | ColB | ColC
==================
  1  |   2  | ABC
  1  |   3  | ABD
  1  |   4  | ABE
  2  |   2  | ACC
  3  |   2  | ACD
  4  |   2  | ACE

I have an example query something like

SELECT ColA, ColB FROM MyTable WHERE ColC LIKE 'AB%'

This clause appears in many selections of varying complexity.

The problem I have is that the ColC codes are going to be changed incrementally into different values, e.g. ABE may be changed to ZBE.Throughout this period, the existing query results have to remain the same - i.e. ABC, ABD and ABE would have to become ABC, ABD and ZBE. Then ABD may change to ZBF and so on. Oh, and the database must have minimal down-time.

My gut instinct would be to change all occurrences of this clause into a function now when time-pressure is low, so that when data starts being transformed, I can change that function into whatever hideous thing it needs to be without having to worry about making lots of complex, messy changes quickly.

However, I've been reading up and come across many recommendations that I shouldn't be doing this as it will be inefficient (which I can totally see).

So, what would be the best way to do this? If I have to accept a period of inefficiency during this transition period, then so be it, but I'd rather not.

Thanks.


texts: User created new login - how to find where they got permissions from

texts:
One of our developers just mentioned how he created a login on a server. I didn't think he should have permissions to do that, so I went and checked.

I've looked at which logins have sysadmin, and he's not a member of any of those groups. There are no logins in the security admin role.
There also don't seem to be any ALTER ANY LOGIN granted in the sys.server_permissions table.

Is there anything else I can look for here?


texts: Storing revisions in SQL Server (simply much like TFS)

texts:
So, what I've got so far:

Table: ChangeSets

Columns: Id | Comment | Committer

Table: Customers

Columns: Id | Name

Table: CustomerChangeSets

Columns: Id | CustomerId (FK) | ChangeSetId (FK)

So now I can have multiple customer changes in one changeset (again very much like TFS). Every changeset in a customer object now results in a new customer object being created (and a new Id assigned due to an identity column).

My problem now is, that I don't know how to represent the mapping from and to my customers. I've got several tables that reference to Customers.Id, but that'd mean I would have a reference to an old version of a customer (the initial one, without any changes).

What's the best way?

Don't create a new customer on each checkin? But how would I get my
required logic then?
Update all the foreign keys to my new Customer.Id? That'd take
quite a long time.


texts: Replacing an aggregation system of statistics

texts:
We currently have a database under MySQL, storing aggregated statistics in different tables (recent hours, hours, days, months).
The tables are updated by workers running at different rates depending on the freshness required for the data.
Then those aggregates are queried by the applications with usually queries involving more even more aggregation.

This solution is showing limits in performance, scaling and flexibility when it comes to queries the data.

Our goal is to replace it with a system based on events sourcing.

Our first prototype use Dataflow (a bit like MapReduce but working in streaming) to pre-compute aggregates for a part of the data and put those in BigTable and put raw events (partitioned) in BigQuery for the aggregations we can’t pre-compute.

The system is globally working but its cost is prohibitive, with an estimated of 25K$/month for BigQuery only.
The cost is mainly due to the high number of queries for which we cannot pre-compute an efficient aggregate (usually we cannot pre-compute an aggregate because we need an earlier event that dataflow doesn’t have at the processing time of the event) .

As alternate back end of BigQuery we tested few other options like kudu, clickhouse , spanner…
So far only clickhouse actually performed very well with sub second response time, but maintaining a clickhouse cluster seems to be a bit hazardous.

From there where else could we look? Did we do a fundamental error in the design that would explain the poor performance? Is it even possible to balance cost usability of such system
I have the feeling that the most used solution is still big hadoop clusters.

Few technical info under normal load:

~300 event/s (with regular pick at 800 event/s)
~34000000 event/day
an event encoded in protobuf weight ~200B
an event has 20 properties
computing the last state can require 3-10 events spread over a range time of 3 months


texts: Getting milliseconds elapsed during a stored PL/SQL procedure

texts:
I've been using sysdate to time procedures, but a lot of my procedures are very small procedures that get called thousands of times.

Is there a way to get milliseconds elapsed instead of seconds? I know it has something to do with timestamps, but I can't find any exact way to do it.


texts: What types of locks are held on global temporary tables?

texts:
I am wondering if:

the isolation level is Read Committed
data is inserted or deleted or read

are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table as the traditional row store table.

For temporary table I believe there will be no blocking as it "lives" during the current context, so I am not able to read and insert data at the same time.

But for global temporary table, let's say I have many operations - some are inserting new data, some are deleting data, and some wants to read data is going to be deleted.

Let's say the structure of the table is:

GroupID EntityID
1       101
1       102
1       103
2       101
2       104
3       100

Are the same rules applied here?


texts: Insert statement performance in SQL Server

texts:
I have the below Insert statement query as part of an stored procedure that is taking some time to be complete (20-30) minutes, when normally in another server with same hardware specifications takes 1-2 minutes

Query:

INSERT INTO [DBO].[cond]
    (
       [LAST_NAME]
     , [FIRST_NAME]
     , [MIDDLE_NAME]
     , [BUSINESS_NAME]
     , [TIN]
     , [date_of_birth]
     , [OCCUPATION]
     , [CTR_TRANSACTION_FK]
     , [CREATED]
     , [CREATED_BY]
     , [LAST_MODIFIED]
     , [LAST_MODIFIED_BY]
     , [CTR_FK]
     , [CUSTOMER_FK]
     , [cust_type_FK]
     , [gender]
     , [naics_code]
     , [name_suffix]
     , [alternate_name]
     , [extra_type]
     , [ID_ISSUING_COUNTRY_FK]
     , [id_issuing_state]
     , [ID_TYPE]
     , [ID_NUMBER]
     , [FLEX_1]
     , [FLEX_2]
     , [FLEX_3]
     , [FLEX_4]
     , [ID_TYPE_OTHER_DESCRIPTION]
     , [person_involved_source]
     , [person_involved_date]
     , [DEPOSITOR]
    )
    OUTPUT
   [INSERTED].[ID]
     , [INSERTED].[customer_FK]
     , [INSERTED].[CTR_TRANSACTION_FK]
     , [INSERTED].[CASHTX_CUSTOMER_FK]
       INTO [#COND_INSERTED]
    SELECT
   [cust].[last_name]
     , [cust].[first_name]
     , [cust].[middle_name]
     , [cust].[COMPANY_NAME]
     , [foi].[identification_number]
     , [cust].[birth_incorporation_date]
     , [cust].[occupation]
     , NTRX.[CTR_TRX_ID]
     , @FilingDateTime
     , @Filer
     , @FilingDateTime
     , @Filer
     , NTRX.[CTR_ID]
     , [cust].[id]
     , [cust].[customer_type_FK]
     , [cust].[GENDER]
     , [cust].[NAICS_CODE]
     , [cust].[SUFFIX]
     , NULL
     , CASE
       WHEN [cashmap].[cb_type] = 'C'
       THEN 'Conductor'
       WHEN [cashmap].[cb_type] = 'O'
       THEN 'Common Carrier'
       ELSE ''
   END
     , [ctry1].[id]
     , [cust].[id_issuing_state]
     , [cust].[id_type]
     , [cust].[id_number]
     , [cashmap].[FLEX_1]
     , [cashmap].[FLEX_2]
     , [cashmap].[FLEX_3]
     , [cashmap].[FLEX_4]
     , [cust].[ID_TYPE_OTHER_DESCRIPTION]
     , 1
     , [cust].[LAST_MODIFIED]
     , [cashmap].[DEPOSITOR]
    FROM  [DBO].[cashcust] AS [cashmap]
      INNER JOIN [DBO].cust AS [cust] ON [cashmap].[CUSTOMER_FK] = [cust].[id]
      LEFT JOIN [DBO].custf AS [foi] ON [foi].[customer_FK] = [cust].[id]
      LEFT JOIN [DBO].country AS [ctry1] ON [cust].[id_issuing_country] = [ctry1].[fincen_code]
      INNER JOIN [DBO].trans AS [TXN] ON [txn].[UNIQUE_TRANS_ID] = [cashmap].[transaction_id]
               AND txn.INSTITUTION_FK=cashmap.INSTITUTION_FK
      LEFT JOIN [DBO].custacc AS [custmap] ON [txn].[account_FK] = [custmap].[account_FK]
                AND [custmap].[mapping_type] = 1
                AND [custmap].[IS_ACCOUNT_HOLDER] = 1
                AND [cashmap].[customer_FK] = [custmap].[customer_FK]
      JOIN [#TMP_INSERTED] AS NTRX ON NTRX.TRX_ID=[txn].[id]
    WHERE  [cashmap].[conductor_bene] = 'C'
      AND @SourceConBenFromTellerFile = 0
      AND ISNULL([cashmap].[has_error], 0) = 0;

Execution Plan:
I noticed that the heaviest operator is the Clustered Index Insert.

Note: First set of inserted row was: 4810
      Second set of inserted row was: 4886
      Third set of inserted row was: 62

So not sure why is taking that time to insert them.

Important Server Info:
MaxDOP = 4.
Cost Treshold of Parallelism = 5

Top Waittypes:

Page life expectancy  =                                                                                60

Also I noticed some implicit conversion warnings in some of the insert statements:

Full Execution Plan:
Part 1:

Part 2:

Part 3:

Memory Clercks for Blocking:


texts: An aggregate may not appear in the WHERE clause

texts:
I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any progress.

   SELECT wd.SAPID, rd.RequestID, vd.VersionTableID,rd.ProjectReference,contactinfo.FirstName,contactinfo.LastName,
                 wd.ActualEndDate, wd.IsIndependentContractor,we.AssignmentStartDate, vd.VersionNo, we.CreatedDate, we.UpdatedDate
   FROM RequestDetails rd
   INNER JOIN  WorkerDetails wd ON  rd.RequestTableID = wd.RequestTableID
   INNER JOIN WorkerExtension we ON we.WorkerID=wd.WorkerID
   INNER JOIN dbo.Address_Info addressinfo ON addressinfo.WorkerID = wd.WorkerID
   INNER JOIN dbo.Contact_Info contactinfo ON contactinfo.WorkerID = we.WorkerID
   INNER JOIN VersionDetails vd ON   vd.RequestTableID = rd.RequestTableID
   WHERE we.AssignmentEndDate >= '2/16/2016' AND EndDate <= '2/20/2016' AND contactinfo.FirstName = 'Barsegyan' AND we.UpdatedDate = (SELECT MAX(we.UpdatedDate))
   GROUP BY wd.SAPID, rd.RequestID, vd.VersionTableID,rd.ProjectReference,contactinfo.FirstName,contactinfo.LastName,
                 wd.ActualEndDate, wd.IsIndependentContractor,we.AssignmentStartDate, EndDate, vd.VersionNo, we.CreatedDate, we.UpdatedDate

A sample of the data I'm querying:

And the error:


texts: Why index REBUILD does not reduce index fragmentatation?

texts: I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It seems that this happens especially with small indices.


texts: Retrieve value of select query in a table column

texts:
I need an alternative to cursor/while loop for below query:

We have a table as given in below screen shot containing the following columns: ID, Module, Query.  The table contains nearly 100,000 records.  Currently we are using cursor and a while loop to insert the output of the select query to a temporary table which we are joining with the actual table to update based on ID.

I need a help in writing a query that takes the Query column as input and gives the result as value like a function.

I tried creating a function which executes dynamic SQL but it failed because EXEC and sp_executesql aren't allowed within a function. Attempts to use a stored procedure also didn't work because we can pass only one query as a parameter from this table to get output as value.

Each query returns a single value (one column, one row). Different queries may return values with different datatypes; all should be converted to nvarchar.

Any solution would be greatly appreciated.


texts: SQLAnywhere: How to import empty columns (date)

texts:
After unloading from a database I've got inside my reload.sql something like that:

INPUT INTO "dbo"."mytable"
FROM '/tmp/316.dat'
FORMAT ASCII
BY ORDER;

The table is defined as something like that:

CREATE TABLE "dbo"."mytable" (
    "id" bigint not null default autoincrement,
     --- ...
    "date" date null,
    primary key ("id")
);

Now it appears, that inside the 316.dat the date-columns are empty. So something like that:

'1121762720','0','0','1','0',,
'7010316461','0','0','1','0',,
'7010238207','0','0','1','0',,
'7010290179','0','0','1','0',,
'7010024210','0','0','1','0',,
'7010030708','0','0','1','0',,
'7010081564','0','0','1','0',,
'7010130267','0','0','1','0',,
'7010264668','0','0','1','0',,
'7010335394','0','0','1','0',,

When inserting the ,, it looks like SQLAnywhere is not recognizing this as field and tries to skip it. Any hints on what's the correct way here? Maybe adding some special parameter to unload?