texts: MySQL 8.021 = 100% CPU when many UPDATE queries to 1 table

texts:
I just install fresh LEMP stack at Freebsd 12 with MySQL 8.021
Noticing high usage of CPU
I tested my queries in normal conditions individual and every is about 0.2s execute time - which is absolutely fine for me, but...
My PHP script run VERY SIMPLE queries:

update set value=0 in client_table;
update set value=1 in client_table;
loop 10 queries - update set about 10 fields in client_table, each query = 1 specified row with WHERE clause;
(when I loop 100 queries its more than 60s and I got timeout because of cloudflare also... - their limit 90s) in general I dont want to exceed 60s because of cron
I use 10 instances of script by crontab every 1 minute

1 instance run: 20-25s
10 instance run: 40-50s EACH script so at least double
The fact is when I raise number of queries from 50 of each instance to 100 it getting almost 3 time more than 1 instance run.
mysql table has almost 30.000 records, about 20 fields tinyint - int, 5x datetime, 1 text max 64k chars - most of fields are empty now so definitely not much data there
- top
PID, USERNAME, THR PRI NICE   SIZE    RES STATE    C   TIME, WCPU COMMAND
67805 mysql,  60  20    0  4454M  1147M select   0   3:29 106.81% mysqld
ps. at most CPU is 20% user, because WCPU shows all cores usage (six)
so maybe it's not really big CPU usage, rather clogging when much queries to 1 table at same time?
I use Contabo VPS with 6 virtual cores and 16GB of RAM and a 400GB ssd disk
I think my script even with loops and all that stuff shouldnt be that much harmful for MySQL... I hear about 8.018 but from ports its 8.021 actually.. so all things should be fixxed.
my.cnf
Edit:
** The modified config I tried: (switched now for mysql 5.7 - but same situation so I doubt it's mysql VERSION issue) **
Maybe you have any performance tips for my config file to serve big more queries to 1 table in same time or maybe u experienced such problems?
My queries from script:
# 1 update
UPDATE products SET available_worker_id = 0
    WHERE available_worker_id = 1

# 2 update
UPDATE products SET available_worker_id = '1'
    WHERE check_available_date <= NOW()
      AND available_worker_id = 0
      AND product_id > 0
    ORDER BY check_available_date ASC LIMIT 100

# 3 update
SELECT product_id, next_check_avail FROM products
    WHERE available_worker_id = '1'
    ORDER BY check_available_date ASC LIMIT 100

while ($item = mysqli_fetch_assoc($result)) {
    $mysqli->query("UPDATE products SET shop1-shop10 = $TINY_INT, available_date = NOW(), check_available_date = '".date("Y-m-d H:i:s", time() + $item['next_check_avail'])."', available_worker_id = 0
    WHERE product_id = $product_id
    LIMIT 1")
}

** DEBUG SQL: (2 instances running at once) **
Max_used_connections    4
Max_used_connections_time   2020-10-27 19:03:02
Delayed_insert_threads  0
Performance_schema_thread_classes_lost  0
Performance_schema_thread_instances_lost    0
Slow_launch_threads 0
Threads_cached  3
Threads_connected   1
Threads_created 4
Threads_running 1
10 instances: Max_used_connections  11
Threads_connected   11
Threads_created 11
Threads_running 10

TABLE: https://pastebin.com/raw/qQPWR3mD


texts: I've heard it being used in relation to SQLite sometimes but never completely understood what it means.


texts: Stop logging INSERT INTO statements in PostgreSQL (but keep SELECT)

texts:
Using logging_collector = on and log_statement = 'all' (from postgresql.conf), currently my log files are correctly populated with every single query that has been executed.

This works great. Except... I don't want to log the INSERT INTO queries, because in this case, such queries are very big, and very frequent. They tend to 1. pollute all the logs and 2. take an enormous amount of space on the disk.

Among the different options for log_statement, which are none, ddl, mod and all, I can't see anything that would log all statement except data-modifying ones such as INSERT INTO.

More concretely, here are some of the things I need to log, or not to log:

statement                                          desired action
--------------------------------------------------------------------
INSERT INTO ...                                    DO NOT LOG
UPDATE ...                                         DO NOT LOG
SELECT                                             LOG
BEGIN; declare cursor with hold for SELECT ...     LOG
EXPLAIN SELECT                                     whatever
CREATE TABLE                                       whatever (preferably don't log)

Is there a way to achieve something that resembles this?

If there is no way to do it through settings, I'll have to resort to some ugly workaround such as running a cron job every 10 minutes which goes into each log file and deletes everything that resembles an INSERT INTO statement... Would be nice if PostgreSQL could avoid me that.


texts: Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints

texts:
I'm designing a database from which old data has to be purged regularly for legal reasons, and I'm trying to figure out the best way to organise the purging.
Dealing with the main tables is trivial, since they all have a month column and cascading deletes are set up for all owned rows in detail tables. So I can simply iterate over a list of these "master" tables and delete rows older than a given month.
However, there are some tables for which things aren't that simple. They can be referenced from quite a few other tables but their rows have to disappear when they are no longer referenced (because of data protection laws).
I could write some code find out which rows in a certain table aren't referenced anywhere else, based on the foreign key meta data in the database schema.
However, I'd rather lean on the foreign key constraints instead and simply use the moral equivalent of DELETE FROM @TableName. The constraints keep referenced rows from getting deleted and all unreferenced rows disappear as intended. Hence I would make simply make a second list of table names to which the unconditional DELETE should be applied during a purge, and that's it.
That solution would certainly be ideal: you can't make it any simpler, and it is directly based on the declared database schema, i.e. the foreign key constraints.
Would that be considered acceptable practice? Are there any drawbacks*?
*) apart from the fact that the consequences of someone dropping a required foreign key constraint are not only unusually dire but also unusually delayed (until the next purge at the end of the year)
Additional considerations
I found the fly in the ointment: the constraint not only blocks the deletion, it also results in an error state and abortion of the current statement. Hence the scheme doesn't work as is. Back to the drawing board then... Any pointers welcome.


texts:
I'm a bit confused on the concept of weak entities and normalization and couldn't find any answers online. I know that generally weak entities have a partial key to uniquely identify its own records but the primary key is usually a composite key made up of this partial key and the foreign key (that refers to the weak entity's parent's primary key). Before I thought we could just have the foreign key be the sole primary key for the weak entity but apparently this is not such a good idea.

I am confused however on normalization, specifically 2NF where we have to make sure that no non-prime attribute is solely dependent on a subset of the primary key.

I'm missing something crucial in my understanding here. So wouldn't a weak entity be in violation of 2nf? Because a non-prime attribute could be dependent solely on the foreign key?


texts: Databases design decision - Mobile & Web

texts:
This may be a question a little broad but... I'm currently having the following dilemma:

Having a web application (already deployed) and a new mobile app, I'm currently working on the mobile app database of the server side and my question is... Do you think it is better to have separate databases for the web & app, or is it better to integrate them in a single one?

I can think of pros & cons for every approach. For example, a single DB is easier to update and mantain. However, keeping them separated gives every app some sort of independence, allowing you to, for example, change the DBMS of any of them, if you need to...

I know it's a bit difficult to respond without knowing the databases design, context, etc. but what is your general thought? and what do the "big companies" usually do?

EDIT: Also, for example, if we wanted to scale up the mobile app in the future and migrate it to another server... would it be harder in any of the previous scenarios?


texts:
EDIT: PLEASE DON'T CONSIDER ARROWS :)

statements are

"A teacher can teach many courses. A student
can enrol in many courses. A course may
be a part of one or many programmes. A
teacher can be mentor of many students,
however a student can have only one
mentor."

Having confusion is Mentor an entity or not

I have drawn two E-R diagrams with

and without mentor

this diagram can be accessed here


texts:
I have a linked server that connects one SQL Server to another SQL Server. For two weeks now there has been an error when testing the connection;

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient
Data Provider)

I have posted about this issue when I first received the user was complaining about it. The projected solution was to manually register the SPN. Our environment already has SPN registered to the service account names for each of the servers. Below are the ways me and my team troubleshooted:

Used the Kerberos Configuration tool to check if there were any issues with SPNs. Yes there was in fact an issue with multiple SPNs which were shown as "Misplaced" in the Kerberos tool. So we updated the SPN's and the status returned to "Good"

Checked CMD -setspn -l to view the list of the services and the service we are looking for with the correct port number is one of the service account under the SPN.

Restarted the SQL Server services to make sure the updates take full effect.

Used DMV to find out the authentication scheme being used. Kerberos authentication is being used.

Queried the data the user has requested to see that the anonymous issue is still present.

Tested the connection to the linked server and the anonymous issue is still present.

Checked the error log to see an error that states:

The SQL Server Network Interface library could not register the SPN
[XXXX] for the SQL Server service. Windows return code: 0x2098, state
15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.

I have notified the Windows AD team to check the delegation status. He has confirmed that the delegation is set to: 'Trust this user for delegation to any service (Kerberos only)'
I am running out of ideas on how much further I can troubleshoot or where the problem lies. If anyone has any other idea, please feel to share and give some feedback. What else can I do to resolve this issue? This issue has been persistent for 2 weeks now. And the users really need to access the data they need for their daily work. Thank you in advance.


texts: Not a able to kill the DB session in Postgres RDS

texts:
We are trying to killing  one of DB session but not able to do it. Tried with following command getting "TRUE" after issuing command. Still we can see the session is active.

select pg_terminate_backend(933);

pg_terminate_backend
t (1 row)

session:

933 | -1 days -01:51:38.165893 | test | SELECT col1,col2,col3 FROM test_table "PC0" WHERE ( col1 LIKE $1 ) ORDER BY 3 ASC;

can anyone please help me to resolve this.
We are using Postgres-9.3 RDS


texts: Purge old records from mysql ibdata1 file

texts:
Is it possible to purge old records from an ibdata1 file without taking down the database attached to it?

Records that have been deleted from the database are still present when I scan ibdata1 for strings.

I am somewhat aware of how the ibdata1 file works and realize that those portions of the file will probably be overwritten in time, but I need to zero out or otherwise remove the old data so it is no longer stored on the server in any form.

Is there anyway to do this without taking down the database?


texts: CREATE DATABASE AS COPY does not work with Azure

texts:
I'm new with T-SQL and MSSQL but need to copy Azure SQL database from one server to another.

As I googled here - it can be done with a CREATE DATABASE Database1_copy AS COPY OF server1.Database1; query, but in my Vusial Studio Code editor on the Ubuntu Linux with the vscode-mssql extention - I have an error:

  Msg 156, Level 15, State 1, Line 1 : Incorrect syntax near the keyword 'database'.

My full query looks next:

CREATE DATABASE Database1_copy AS COPY OF oldserver.database.windows.net.olddatabasenamehere;

Additional googling leads me to the same solutions (here is another example I found).

What I'm doing wrong here?

I understood that even the first link published on Azure's resource (azure.microsoft.com) - it does not necessarily is working solution for Azure's SQL.

P.S. Idea is to automate DEV environment rollout (with ARM templates) as a copy of the current Live environment, with creating databases as a copy of Live's databases.


texts:
What's the best way to approach modelling a warehouse picking system?

To be precise, customers place orders which are processed by warehouse staff.

A simple products and orders database isn't an issue. My trouble comes breaking down orders into smaller warehouse trips. Tips are created based on the quickest route around the warehouse based on product locations. Two assistants can pick different parts of the same order (different trips), but they can also be picking multiple orders.

I can't wrap my head around how I'd make a simple model of this, I know I need to break an order down into a smaller order(s) based on whatever criteria (warehouse location in this case).

I think it'll be along the lines of (some obvious fields missing for clarity):

TRIPS {
TripID
AssitantID
SmallOrderID
}

ORDERS {
OrderID
ProductID
Quantity
}

SMALL_ORDERS {
SmallOrderID
OrderID
}

I know this isn't right as the SMALL_ORDERS isn't broken down, I'm just duplicating ORDERS. Would I make SmallOrderID on the TRIPS table, contain multiple values or is this a bad idea?

This is solely for a self-learning programming project, I'm not looking for anything enterprise-like. It's a simple version of a major enterprise system we use where I work.

I'd be more than happy if anyone can tell me the correct type of schemes I should be googling for, I assume they must have a certain name.

thanks.


texts: Grouping by sequential relationships with Postgres

texts:
This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following relationships as key pairs

a -- b -- c      e -- f -- g
     |   /
     |  /
      d

This is the table with example data (also on SQLFiddle):

CREATE TABLE relationships (
   name varchar(1),
   related varchar(1)
);

INSERT INTO relationships (name, related) VALUES
('a', 'a'),
('a', 'b'),
('b', 'b'),
('b', 'a'),
('b', 'c'),
('b', 'd'),
('c', 'c'),
('c', 'b'),
('c', 'd'),
('d', 'd'),
('d', 'c'),
('d', 'b'),

('e', 'e'),
('e', 'f'),
('f', 'f'),
('f', 'e'),
('f', 'g'),
('g', 'g');

What is the most efficient way to produce an output that looks like this:

| group |    members    |
------------------------|
|   1   |  {a, b, c, d} |
|   2   |  {e, f, g}    |

or this:

| name |  group |
-----------------
|  a   |   1    |
|  b   |   1    |
|  c   |   1    |
|  d   |   1    |
|  e   |   2    |
|  f   |   2    |
|  g   |   2    |

I have thought about doing this operation outside of Postgres, but it seems like there must be a way to achieve this result with either a window function or PL/pgSQL.


texts: update table with replace string1 string2

texts:
I need replace string1 to string2 in column big tabe.
My table weight 1.7GB. For update I will be use:
UPDATE table SET column = REPLACE( column, 'search', 'replace' ) ;

So, I have several questions:

Will table lock during procedure? Should I hide public part my site or not?
How long time this procedure can executing?
How better run this? Maybe 'screen'? if my ssh connection will closed.

Mysql 5.5.53


texts: Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)

texts:
I'm struggling to find out how we can store the conditions inside a conditional split transformation when we run a package. I need a snapshot of the expressions (the whole expressions, not only parameters inside them).

The closest we have got is when an error triggered the SSIS logging and stored the context in the table event_message_context, but we can't figure out how to trigger this logging without an error. We don't need it specifically to come from the built-in logging, we can surely use a script task if you have any suggestions. We run both SQL server 2012 and 2016.


texts: Insert if something changed other than specific column

texts:
I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed?

ALTER TRIGGER [dbo].[t_upd_insert]
ON [dbo].[Player]
FOR INSERT, UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;

BEGIN
INSERT INTO dbo.Changes (
            [name]
            [created_at]
       )
SELECT
            ins.[name],
            ins.[created_at]
FROM INSERTED ins

I could do a WHERE name != ins.[name] for each column but there are a lot of columns and tables that need this; is there a shorter syntax?

EDIT
 I've tried adding

WHERE
      [name] != ins.[name] OR
      [age] != ins.[age]
      ....

but that doesn't work either. I thought that would be the naive method but that doesn't work.


texts: Different Disk Drive Same Backend Storage

texts:
I have a database running on a VM which is getting hammered during a large load, in particular I can see waits of WRITELOG happening.  My initial thought is to split the files out on to their own drives but the backend storage is the same as where the other DB files are sitting.

Basically it is SAN presented as a Cluster Shared Volume to a whole host of virtual machines.

Would there be a performance advantage in doing this?  Some memory in the depths of my brain is telling me something about the number of IO streams would be better potentially?

To update this I have now separated out the files and correctly sized the transaction log.  I have been collecting information from sys.dm_io_virtual_file_stats and can see that I now have extremely high readIOStalls but with a low latency of 13ms.  I also collected some memory information and PLE was in the thousands on average with this being a 32GB system I would expect that apart from in one 30min period where it drops right down to 30 before climbing sharply again, at this time lazy write/sec increases to 50 also before reducing to 0.   Could this period be the cause of the large number of read stalls I am seeing?  I would have expected to see with such high Read Stalls also high latency?


texts: Help with SSIS Framework- Resolve Referrences in Data Flow

texts:
LineageString and StageKey are both unmapped. SSIS is giving me an error between the OLE DB Source and the Add metadata.

Add Metadata.Inputs[Derived Column Input].Columns[LineageString] has lineage ID 175 that was not previously used in the Data Flow task.
"Add Metadata" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Framework from https://anotherssisframework.codeplex.com/

Pictures


texts:
I have a table of jobs that has columns for unique id, app, env, the status of the job and the timestamp for it, along with other data not relevant to the question. Like so,

CREATE TABLE the_table (
    id       int           AUTO_INCREMENT,
    app      varchar(255),
    env      varchar(255),
    time_end datetime(3),
    status   varchar(255),
    PRIMARY KEY (id)
);

Each job belongs to an app in en environment, env, and the values for these are limited, currently around ten for each of app and env. Some example data (with changed values):

INSERT INTO the_table(app,env,time_end,status)
VALUES
    ( 'app1', 'env1', '2017-11-14 14:45:07.037', 'finished' ),
    ( 'app1', 'env2', '2017-08-01 08:30:03.951', 'finished' ),
    ( 'app1', 'env1', '2017-10-05 21:36:22.716', 'finished' ),
    ( 'app2', 'env2', '2017-05-18 11:26:58.122', 'finished' ),
    ( 'app1', 'env1', '2017-09-26 00:35:37.914', 'finished' ),
    ( 'app1', 'env3', '2017-11-05 20:27:00.762', 'finished' ),
    ( 'app1', 'env3', '2017-06-04 16:55:19.089', 'finished' ),
    ( 'app3', 'env3', '2017-10-05 04:36:00.820', 'finished' ),
    ( 'app3', 'env3', '2017-09-22 04:11:51.602', 'finished' ),
    ( 'app2', 'env1', '2017-10-09 15:36:38.642', 'finished' ),
    ( 'app1', 'env2', '2017-10-04 16:07:48.272', 'finished' ),
    ( 'app1', 'env1', '2017-09-16 15:35:22.239', 'finished' ),
    ( 'app1', 'env1', '2017-10-12 19:53:29.434', 'finished' ),
    ( 'app1', 'env1', '2017-08-23 07:07:56.983', 'finished' );

I want to get, for each combination of app and env, and status=finished, the latest record. My first solution would be something like:

xs = Query("SELECT DISTINCT app FROM the_table")
ys = Query("SELECT DISTINCT env FROM the_table")

for app in apps do{
  for env in envs do{
    r = Query("SELECT * FROM the_table WHERE status='finished' AND the_table.env=env AND the_table.env=env ORDER BY time_end desc LIMIT 1")
  }
}

This could potentially be a lot of queries though and I would like to find another solution more idiomatic to SQL. This is using MySQL 5.7, so I can't use limit in subqueries.


texts:
I have two tables one for property detail and other for amenities. In amenities i have inserted data one by one means it has three column amenity_id,property_id,amenity. Now I want to get the property which amenities are kitchen and bedroom and Breakfast and internet etc. My created Query is like:

SELECT
    property.*,
    property_type.property_type
FROM property,property_type,apartment_amenity
WHERE property.approval='Y'
    AND property.propertytype = property_type.property_type_id
    AND property.country like '%india%'
    AND (property.city like '%indore%' OR property.state like '%madhya pradesh%')
    AND property.Maximum_Capacity >=1
    AND property.pid = apartment_amenity.property_id
    AND apartment_amenity.amenity_id in (1,2,3)
ORDER by is_online_bookable DESC
LIMIT 0,10;


texts: Duplicate Inserted Row into the new table when running trigger

texts:
I create 2 tables like this:

CREATE TABLE task
(
 task_name TEXT NOT NULL,
 duration INT,
 UNIQUE(task_name)
);
CREATE TABLE temptable
(
 task_name TEXT NOT NULL,
 duration INT  NULL,

);

Then I insert one row in 'task' table.

INSERT INTO public.task(
    task_name, duration)
    VALUES ('task1', 10);

I want to insert a new same row into temptable table every time I add new record into task table
So I create a trigger to insert automatically data in 'temptale' at the same time

CREATE OR REPLACE FUNCTION update_temptable()
RETURNS TRIGGER AS
$$
BEGIN
    -- insert temporary row
    INSERT INTO temptable(task_name, duration)
    SELECT NEW.task_name , NEW.duration FROM task;
   RETURN NEW;
END
$$
LANGUAGE 'plpgsql';
/* Create trigger call function*/
CREATE TRIGGER ins_new_record
    AFTER INSERT ON task
    FOR EACH ROW
    EXECUTE PROCEDURE update_temptable();

But when I insert a new row ('task 2',10) into 'task' table, the records inserted into 'temptable' table always duplicated like this.

task_name|duration|
|"task2" |10      |
-------------------

|"task2" |10      |

I don't know how to fix it. Can anyone help me? Thanks.


texts:
I have a query which generates a result set (key, a, b).

I want to use an update statement where, for each entry in a table with Tkey = key, set ColA = a, ColB = b.

How would I do this?

Right now I'm trying:

UPDATE table1
set ColA = a,
ColB = b
where TKey in (select TKey, a, b from ...)

but a and b can't reference the where clause, right? So how can I do this?


texts: Does rebuilding a clustered index on an IDENTITY column do anything useful?

texts:
We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and the server ran out of disk space so I've been up since 12:30 am.

Now I'm wondering does SQL Server even do anything when it rebuilds this index? There are no leaf pages or page splits since all the inserts go into the last page. And we never delete rows (not yet anyway).

Obviously the server tried to do something, but if it had succeeded would the table be any different? I am thinking this is just a giant no-op and a waste of time. Obviously this is not true if the table has non-clustered indexes as well. And actually if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?

My DBA says he manages all the indexes at the database level, not at the table level.

Additional information

My assumption is that after the rebuild the pages on disk look the same as they did before and we're just wasting our time database shuffling.


texts:
I have a client whose Postgres database I need to connect to periodically. This database is currently hosted on their server, let's call it psql.example.com, with standard port 5432. They have it configured so that I can connect to it from my "primary" server, let's say that's on IP 123.45.67.89, using my_username and my_password as credentials.
The thing is, it's not my primary server that need to connect to it, but rather my load-balanced AWS nodes. It seems that I should be able to run something on my primary server with ssh -L or iptables, which will listen for incoming connections (on a different port, say 42042, to avoid potential conflicts later on) from my load-balanced nodes (easy to secure that end of things with standard AWS functionality), and transparently forward it on to their database, passing any response back.
I've tried things like ssh -L 42042:psql.example.com:5432 my_username@123.45.67.89, but this doesn't work; if I debug, I see that it's trying to connect to port 22, not 5432. Seems that I shouldn't be the only one wanting to do something like this, but most of what I've found is about using SSH to secure the connection to the DB server. It doesn't seem like any of the my_username@123.45.67.89 bit should be required on my primary server, that should be more for securing the connection from my end node to my server? Security of the connection to their server would be done through their existing IP limits and then PSQL credentials.
They're running quite an old version of Postgres, 8.1, which I don't think would make any difference for a pure port-forwarding solution, if that exists, but might limit other options.


texts: There are some inserted records on galera cluster that does not exist!

texts:
We are using a galera cluster with 3 node and 2 database on it which they are related to each other by some applications.

The problem is, some records are inserted into table and galera generate id for it although it register in /var/log/mysql.log, but, when, we select that id, the result is empty.

Note: There is no error in /var/log/mysql.err is registered. Even, we check the possibility of deadlock but, the insert query is run by transaction without any rollback and there is no error was registered.

we can't find the the reason and it's solution?

Update:

CREATE TABLE `requests` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `web_service_id` int(11) DEFAULT NULL,
     `status` int(11) DEFAULT NULL COMMENT '0-queue1-successfule2-unsuccessfule3-error',
    `telephone` bigint(20) unsigned DEFAULT NULL,
    `created_date` timestamp NULL DEFAULT NULL,
    `cdr_id` bigint(20) NOT NULL,
    `product_id` int(11) unsigned NOT NULL,
    `host` varchar(50) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `fk_requests_1_idx` (`web_service_id`),
     KEY `fk_requests_2_idx` (`product_id`),
     KEY `cdr_id` (`cdr_id`),
     KEY `created_date` (`created_date`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2482907 DEFAULT CHARSET=utf8

Mysql Version:

 mysql  Ver 15.1 Distrib 10.2.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2


texts: Does DBMS (e.g. of SQL SERVER 2008) use virtual memory?

texts: Or in other words ... . Does the DBMS somehow circumvent the OS Cache manager and acccess the memory directly OR it lets the OS do its job, behave as a normal process and use the concept of virtual memory?


texts:
As per wikipedia: Cardinality refers to uniquess of data:

  In SQL (Structured Query Language), the term cardinality refers to the
  uniqueness of data values contained in a particular column (attribute)
  of a database table. The lower the cardinality, the more duplicated
  elements in a column. Thus, a column with the lowest possible
  cardinality would have the same value for every row. SQL databases use
  cardinality to help determine the optimal query plan for a given
  query.

BUT, Oracle documentation explains it as:
"The cardinality is the estimated number of rows that will be returned by each operation"

So, is this term has different meaning in SQL and in Oracle ? or  i am missing something here ?


texts: Are Stored Procedure failures logged by SQL Server?

texts: How can I see a list of stored procedure errors over a given timespan in SQL Server Management Studio 2008?


texts: MySQL search function join on multiple conditions

texts:
I have a database with two tables studies and elements. I've made a website where users can perform search queries to check if a particular study has used particular elements. If it does, I want the code to return those studies. For example, if the users typed in "Barney Likert" in the search query, it should return 1 row because "Barney" can be found in studies and "Likert" can be found in elements. However, if I type it in it returns zero rows. If I type in "Test", it does return rows because "test" can be found in both tables. What am I doing wrong? The key part of the code is this one:

$sql = "SELECT * FROM studies join elements
                   on studies.S_Element_ID = elements.E_Element_ID
              WHERE ((studies.Author LIKE '%$search%'
                   OR studies.Study_Name LIKE '%$search%'
                   OR studies.Abstract LIKE '%$search%')
                AND (elements.Element_Name LIKE '%$search%'
                  OR elements.Element_Author LIKE '%$search%'
                  OR elements.Element_Source LIKE '%$search%'
                  OR elements.Element_Manipulation LIKE '%$search%'
                  OR elements.Element_Scale LIKE '%$search%'))";

As you can see, I have the values Element_ID in both, these need to match. Moreover, the keyword search needs to be found in both tables. Why does the query not return the right result?


texts: Granting access only to standby database

texts:
One of the physical standby databases in my environment is open in read-only mode and used for reporting purposes .
I want to give users access only to this particular standby, not to primary db.

The best approach I found so far is to create AFTER LOGON ON DATABASE trigger which checks v$instance (or maybe v$database), and throws error for depends on  values of host_name/instance_role columns  .

I wonder if there is a better way to do it.


texts: From a design perspective, what is the difference between an inventory system and an asset tracking system?

texts:
From what I can see, the database design is similar.

One is the consumables that are resident in a warehouse (inventory).  The others are items, capital, that are assigned to people or places, but are not consumable (assets).

For database design, does that sound like the right track to start down?


texts:
Ok, so first off i am going to provide a bit of information on the database structure

I have 3 indexed tables that are used in this query (though one of them should probably be a weak entity, but this is how it was when i took over)

person
person_id (pk)

classes
class_id (pk)
course_id (fk)

learners_to_classes
id (pk)
person_id (fk)
class_id (fk)

The persons Table has about 46,100 records
learners_to_classes has about 51,100 records
and classes has about 1670 records.

The query i am working on is built dynamically. I am working on a person search feature which will list the records from person, plus an indicator if that person has completed 2 particular course types, based on a number of parameters.

there issue i come across how ever is the query time for when the only parameters supplied is either none, (which would probably be a rare use) or a combination of the course types has been completed.

so for example. i want a list of people who have aTrained set to Yes would produce the following query:

SELECT
    p . *,
    IF(sum(c.course_id = 2) > 0, 'Yes','No') as `cTrained`,
    IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as `aTrained`
FROM
    (`person` p)
LEFT JOIN
    (SELECT
        `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
    FROM
        (`classes` c)
    INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 4) > 0
ORDER BY p.`firstname` asc

searching for people who have cTrained='Yes' and a aTrained='Yes' generates the following query:

SELECT
    p . *,
    IF(sum(c.course_id = 2) > 0,'Yes','No') as `connectorTrained`,
    IF(sum(c.course_id = 4) > 0,'Yes','No') as `asistTrained`
FROM
    (`person` p)
        LEFT JOIN
    (SELECT
        `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
    FROM
        (`classes` c)
    INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 2) > 0
    AND sum(c.course_id = 4) > 0
ORDER BY `firstname` asc

These queries how ever are taking over 100 seconds to execute,
and i cant seem to think of a way to optimize them.
I was hoping someone one here might have an idea


texts:
TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse?
So forgive me the broadness of the subject title, as it glosses over details, but I'm trying to understand how ETL and ELT differ in a definitive way. They seem very similar, to the point that you could describe either as ELTL. In both cases you start with raw data and end up with concrete transformed data in tables. It seems to be what happens in between is the difference, which can take many forms depending on the number/complexity of transformations from start to end state.
From what I know of ETL, the steps are as follows:

Extract the data from source systems and load into raw tables
Transform the data from raw tables into staging tables
Load the data from the staging tables into your warehouse

In some definitions of ELT I've seen, the steps seem to be:

Extract the data from source systems and load into raw tables
Transform the data notionally from the raw tables by creating views that represent the staging tables
Load the data from the staging views into your warehouse

But then I look at other articles about ELT, and it seems to be:

Extract the data from source systems and load into raw tables
Transform the data notionally from the raw tables by creating views that represent the staging tables
Congratulations, no more hard duplication of data from raw to transformed state, everything is views! Users just fire off their query and views nested n-levels deep instantly* return data!

*Results may vary, depending on whether you have millions of dollars to throw at cloud processing and/or large-scale quantum computing becomes available


texts: Is ANALYZE useful immediately after creating an InnoDB index?

texts:
Let's create a new index on an existing table:

CREATE INDEX `IDX_some_index`
    ON `some_table` (`a_column`, `another_column`);

Is this useful?

ANALYZE TABLE `some_table`;

Can anyone point to some documentation clarifying whether the CREATE INDEX process analyze the target table or not?


texts: Change posgresql 9.3 slave roll password

texts:
I need to change the password on my slave posgresql server, because the person who set it up left the password the same as the password for the super user account on our other server.

When I run psql as the super user on the slave server, and then attempt to execute the command

ALTER USER username PASSWORD 'newPassword';

I get the error:

  ERROR: cannot execute ALTER ROLE in a read-only transaction

When I attempt to change the transaction with:

SET TRANSACTION READ WRITE;

I get the error:

  ERROR: cannot set transaction read-write mode during recovery

I understand that the slave server can only do reads, though how do I bypass this to change the roles password, or pull the slave temporarily out of the recover mode?


texts: why is this postgresql query returning only zeros?

texts:
select g.ad_id, count(1) as total,
(select count(1) from geoData g1 WHERE g1.ad_id=g.ad_id and
utc_timestamp > '2017-11-01 00:00:00' AND utc_timestamp < '2017-11-01 23:59:59') NOV1,
(select count(1) from geodata g2 where g2.ad_id=g.ad_id and
utc_timestamp > '2017-11-02 00:00:00' AND utc_timestamp < '2017-11-02 23:59:59') NOV2,
(select count(1) from geodata g3 where g3.ad_id=g.ad_id and
utc_timestamp > '2017-11-03 00:00:00' AND utc_timestamp < '2017-11-03 23:59:59') NOV3,
(select count(1) from geodata g4 where g4.ad_id=g.ad_id and
utc_timestamp > '2017-11-04 00:00:00' AND utc_timestamp < '2017-11-04 23:59:59') NOV4,
(select count(1) from geodata g5 where g5.ad_id=g.ad_id and
utc_timestamp > '2017-11-05 00:00:00' AND utc_timestamp < '2017-11-05 23:59:59') NOV5,
(select count(1) from geodata g6 where g6.ad_id=g.ad_id and
utc_timestamp > '2017-11-06 00:00:00' AND utc_timestamp < '2017-11-06 23:59:59') NOV6,
(select count(1) from geodata g7 where g7.ad_id=g.ad_id and
utc_timestamp > '2017-11-07 00:00:00' AND utc_timestamp < '2017-11-07 23:59:59') NOV7,
(select count(1) from geodata g8 where g8.ad_id=g.ad_id and
utc_timestamp > '2017-11-08 00:00:00' AND utc_timestamp < '2017-11-08 23:59:59') NOV8,
(select count(1) from geodata g9 where g9.ad_id=g.ad_id and
utc_timestamp > '2017-11-09 00:00:00' AND utc_timestamp < '2017-11-09 23:59:59') NOV9
FROM geoData g
group by g.ad_id ;

The psql says its ok, it takes almost an hour to run (it's 35gigs of data in the table) but it returns all zeros in the subqueries


texts:
In the Project was used old version of mysql++, recently it was updated.
After compilation I got some error messages:
«class mysqlpp::Query» has no member named «success»
«class mysqlpp::StoreQueryResult» has no member named «purge»
Does anyone know what should be used instead this ?


texts: Statements vs PreparedStatements performance

texts:
I know that PreparedStatements are parsed once, so typically they execute faster.

But consider following query:

select *
  from plc
  join qsn on qsn.id = plc.fk_qsn
  join prs on prs.id = qsn.fk_prs_insured
 where (:ifl is null or qsn.fk_ifl = :ifl)
   and (:prsx is null or prs.display_name like :prsx)

This query prepares some data for a report, and there are some optional inputs on the page to apply some filters on the query result.

As it shows, the ifl and prsx input parameters could be empty, and if they are empty they shall not affect and filter query result.

When I execute this query using a PreparedStatement it takes so long, but when I execute it using an Statement and substitute the parameters in the query string it executes within a second.

I analyzed the execution plans, and I see that when I substitute the parameters in query and execute it as an Statement, the execution plan is so cheaper, because Oracle knows whether each parameter is null or not at parse time, and simplifies the execution plan. But when I use a PreparedStatement the execution plan is as executing multiple queries and unioning the results.

So I want to know if there is a simple way to use PreparedStatement, but have the performance of Statement in executing such queries?

And what are the cons of using the Statement (besides the SQL Injection which I have taken care about it)?

p.s. Someone told me that Oracle query parsing is not done in parallel, so if multiple users execute the report simultaneously, waits will happen, is that true?


texts: PostgreSQL: Add or drop column under load give wrong number of columns errors

texts:
I have a heavily used PostgreSQL database. Sometimes I need to add/remove columns, preferably without any service interruptions.

I follow the safe operations list from https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql but many operations cause troubles anyway when the more busy tables are updated.

Typically we have user defined functions for all operations, which are run in the following manner:

Table and function definitions:

create table a(
    id serial primary key,
    x integer
);

create or replace function select_a() returns setof a AS
$$
begin
    return query
    select a.* from a;
end;
$$ language plpgsql;

Then the actual queries are run by our application as

SELECT id FROM select_a();

However, if I add a column with ALTER TABLE users ADD COLUMN y text; while the system is under load i sometimes (more frequently and persistent the more load the system is experiencing) get errors like these

  ERROR #42804 structure of query does not match function result type: Number of returned columns (2) does not match expected column count (3).

Can this be avoided somehow, or do I need to take the system offline during these kind of changes?

To recreate this follow these steps:

create the above table and function as give above
Create one file loop_alter.sql

#!/usr/bin/env bash
for i in {0..1000}; do
echo "alter table a add column y text; alter table a drop column y;"
done;

Create one file loop_select.sql

#!/usr/bin/env bash
for i in {0..100000} do
echo "select * from select_a() limit 1;"
done;

Run the two files simultaneously with psql

In one terminal: ./loop_alter.sql | psql

In another: ./loop_select.sql | psql


texts: How do I reindex a MYSQL table using a different disk?

texts:
I have my mysql data and index files on disk1

I need to reindex the table and do NOT have enough disk space on disk1

How do I reindex a table on disk1 and use disk2 for the reindex disk space?

For example, table foo is on disk1.  I need to add an index on acctnum but do not have enough disk space on disk1. Therefore, I want to reindex on disk2 but keep the data/index files on disk1

alter table foo add index (acctnum);

edits: (by jcolebrand from an answer)

MyISAM is the engine

MySQL version is 5.0.95-log

I need the database operational during the reindex process, unless there is a quick method to load 4.5 million records.


texts: Opened_tables value and tweaking table cache

texts:
I've been reading on improving performance of my database, and ran into things called table-open-cache and Opened_tables . The MySQL doc told me the following:

Opened_tables
The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Source
What do they mean by big? How much big is big enough to increase cache?
Okay, so I'm checking, how many tables are opened and seeing this:
 ---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Opened_tables | 711353 |

I'm increasing table-open-cache to 32768 (in fact, it was only 2000 before) and opened_tables is still growing.
An hour later, opened_tables is almost 750000.
And 20 hours later, it is 754000, so definitely the growth slowed down significantly, but still going.
Also, number of Open tables in mysqladmin status seems to have stopped increasing, or almost stopped. Well, it still increments by 1 every 5 minutes or so.
Currently mysqladmin status tells me this:
Opens: 754575  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754578  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754585  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754589  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912

I've called it 4 times consecutively, so as you can see that Open tables is not growing, but Opens is growing.
How can it happen, if Open tables is still far away from table_opened_cache value which I set to 32768 about 21 hours ago?
Could you advice anything on tweaking this value and explain the results I got by this moment?
I have plenty of RAM for my purpose, and increasing table cache to 32k made server use 400MB more memory, but I still have 14GB free of 16.
What conclusions should I make at this point?


texts: How to close all query windows in DBeaver?

texts:
DBeaver retains all results & sql editor windows when the program is closed.

The indicator showing tab overflow is easy to overlook (see pic). A user can accumulate an absurd number of tabs if they aren't paying attention & actively closing tabs after each session. I don't see an obvious way to do it and it's impractical to close them one by one.

How can I close all query windows in DBeaver?

I mean 'close' as in save nothing and close the query window completely.


texts:
X  Y  Z
x1 y1 z1
x1 y1 z1
x1 y1 z2
x2 y1 z1

The questions  are :

a. List all the non-trivial original functional dependencies that this relation instance preserves

b. Change a single value to eliminate all FDs that the above instance preserves.

To confirm that there is no typo from my part (could be from the professor):


texts: How to stop logging messages in error log

texts:
How do i stop logging unwanted messages in SQL error logs, I enabled DBCC TRACEON (3226,-1) to avoid logging successful backups, likewise is there any other trace flags or any message id's to disable in msdb.dbo.sp_altermessage to stop them
Below is the example of unwanted messages i want to stop logging

Thanks in Advance


texts: Organizing Relational Database Logical Model with Varying Categories

texts:
I'm having difficulty designing a schema for my postgresql database that relates to the items a store can create.

There are three possible categories: (1) Clothing (2) Footwear (3) Other.

The problem is that each clothing item can vary in the colours available, the sizes available, and number of images. Meaning a user can click to create an item, and then continue adding more available colours with their respect size and quantity. Something similar to:

[ITEM TITLE input]

[COLOR input]
    - [SIZE input] - [QUANTITY input]
[IMAGES input]
[+ to add more colors]

If the only variable value was color, I could just “normalize” the model, and create an available colours table with a relationship back to the item. I'm not sure how to handle the additional variable of available sizes. What's more, the Other category wouldn't have colours or sizes available to it, only quantity.

Does anyone know if there's a solution to this type of problem, or a design pattern that I could read up on that might apply? I'm trying to wrap my head around how I could design this model and I'm coming up blank. Ideally I'd be working with a nosql database in this case but that's not possible.


texts:
I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD.

But I am not sure if I have too many indexes.

My application allows users to upload products for sale. So the table that will be queried the most, Select / inserts is my products table.

The Table looks like this, (Removed a few rows in this example).

Product

p_id - (int) Primary key Used to filter products, new/old - asc/desc

state_id - (int) index key & foreign key Links to primary key in state Table in order to get the state name, also used for filtering when searching products

city_id -  (int) index key & foreign key Links to primary key in city Table in order to get the city name, also used for filtering when searching products

cat_id - (int) index key & foreign key Links to primary key in category Table in order to get the category name, also used for filtering when searching products

subcat_id - (int) index key & foreign key Links to primary key in subcategory Table in order to get the subcategory name, also used for filtering when searching products

title - (varchar) FULLTEXT search Used for text search, when searching/filtering products

seller_id - (int) index key & foreign key Links to primary key in users table in order to get the sellers username

product_type - (tinyint) index key Used when searching/filtering products, if a product is an ad or auction

price - (int) index key Used when searching/filtering products

end_time - timestamp

is_active - (tinyint) index key Used when searching/filtering products

So when doing a search for products there will be a total of 4 inner joins , I am not displaying the sellers name in search results. Only inner joining, state / city / category / subcategory.

As for now I have a total of 8 index keys and 5 foreign keys.

My first question: Do I need to put a index key on all columns that has a foreign key?

I am also thinking of adding a few composite index.

composite index 1: (title, state_id, city_id, cat_id, subcat_id, product_type , is_active)

composite index 2: (title, state_id, city_id, product_type, is_active)

As for now it looks like most users only do a text search. So the most common query is a fulltext search where is_active = 1


texts:
I'm trying to create a new user in AWS Aurora Postgres DB.

Create user worked fine and I can login to the DB using that account.
granting permissions to it isn't.

First:

GRANT ALL on ALL tables in schema public to username

returns:

ERROR:  permission denied for relation some_table

some_table owner is a member of the same group the the new user.

Then, trying another grant options:

ALTER DEFAULT PRIVILEGES FOR USER username IN SCHEMA public GRANT ALL ON tables TO group this_group;

returns

ERROR:  must be member of role "username"

Any idea how to solve it out?


texts: Oracle error "too many values", must be solved with nested SELECTs

texts:
I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs.

A simplified description of my problem, on a toy problem describing the hierarchy in a company. There are two tables:

ACG_EMPLOYEES, which lists all the employees

ACG_BOSS_OF, which simply lists who (b_id) is the boss of whom (e_id)

I can do

SELECT b_id as boss,
             LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
             KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
             e_id,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
      FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1

to get the list of "minions" for every employee which is not a leaf in the hierarchy:

But let's say I also want to get other fields from the ACG_EMPLOYEES table, in this case the name for each boss:

I have tried

SELECT
emp.name,
(SELECT b_id as boss,
             LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
             KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
             e_id,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
      FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1) minion
FROM acg_employees emp

but I get the "too many values" error I mentioned.

I have seen some suggestions for solving this kind of issue; however, I cannot use any of them, since this statement will be used in a Product Lifecycle Management app for which this must be defined as a single SELECT, no matter how complicated (ok, I could use JOINs as well, but no stored procedures or such).


texts: Error ORA-12505 when connecting to Database Oracle 11g SQL Developer

texts:
recently I installed Oracle XE 11g with SQL Developer.

When I first ran it and created a database and a new user, everything worked perfectly - I was able to connect to the database and was able to run queries and scripts.
However, when I restart my laptop (for example, the following day), I am unable to connect to my database using that new user I created, as well as unable to use SYS/SYSTEM accounts.
I get an error with the code ORA-12505 that states:

"An error was encountered performing the requested operation:

Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505".

Nothing has changed since I restarted my laptop! I have also Googled the error but I am still unable to solve it. I know the error has something to do with my 'tnsnames.ora' file, but the service_name is correct from what I can tell.

My tnsnames.ora file is as follows:

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Mark-Prescher-Laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

Thank you for any help in advance!


texts: Add constraint on number of entries of certain type in referenced table

texts:
As a personal project I am working on a database to keep track of cars as they are manufactured and then sent to dealerships. One of my constraints is that certain dealerships can only have a certain number of cars of the same make. For example, DealerA can have 12 Hondas, 10 Audis, and 8 VWs. I have a table which keeps track of all the manufactured cars and then is updated with the dealership when it is shipped. I want to add a constraint in the database which stops you from changing a car's dealership field to a given dealer if the number of cars of that type is already at the dealership's limit.

In other words, if the count of cars of typeA assigned to dealerA (in the Car table) is less than typeA constraint for dealerA (in Dealership table) then allow another car of typeA to be assigned to dealer A, otherwise do not allow the change.

I am quite new to SQL and I am using this project as a way to teach myself.


texts: INSERT takes over 5 hours, when inserting more than specific number of rows

texts:
When inserting less then about 1,350,000 rows to the table it all takes about 2 minutes, however when number of inserted rows is bigger, then the time needed to insert data grows to about 5 hours.

The problem is not connected with query or indexes, because everything has been working fine for a long time and nothing has changed in the structure of query, tables or indexes.

Problem appeared for the first time about 2 weeks ago and it appears repeatedly on days, when number of inserted rows is bigger than +-1,350,000. For example on one day number of inserted rows is 1,200,000 and the process takes 2 minutes, on the other day number of rows is 1,450,000 and it takes 5-6 hours to insert data.

I tried to rebuild indexes, but it has not helped.


texts:
We have magento running with MySQL. We're getting problems where queries would take much more time to execute than they should. In example:

 INSERT INTO `catalogsearch_result` SELECT 63 AS `query_id`, `s`.`product_id`, MATCH (s.data_index) AGAINST (' \"cell\" \"phone\" \"data\" \"transfer\"' IN BOOLEAN MODE) AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND (MATCH (s.data_index) AGAINST (' \"cell\" \"phone\" \"data\" \"transfer\"' IN BOOLEAN MODE)) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)

Queries such as that one take sometimes 15 minutes to complete (stuck on sending data on show processlist ), but when I run them independently, over PMA in example, they complete in 3 seconds.. what might be causing that issue?

These queries are Magento-generated, so I doubt it's a problem with syntax

Also, I think that in some parts of the day, these queries execute nicely, but then comes a period when MySQL just gets stuck. show processlist shows a queue of such queries and they are executing sequentially with every one taking 20 minutes to complete.


texts:
Possible Duplicate:
  Test if any fields are NULL

I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column other than writing something like ...WHERE Col1 IS NULL OR Col2 IS NULL OR...? Thanks in advance.

EDIT: The table is on a MS SQL Server 2008 instance. The columns in the table are of different datatypes.


texts: SQL Server Indexing Performance in Unique/Duplicate column

texts:
It is said that if we create index on a column with more uniqueness than performance of
that index will be more. But I believe that whether it is unique or not it will occupy same no. of blocks than why due to uniqueness it will be faster.


texts: mySQL running on 100% CPU both on RDS and EC2

texts:
How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average
mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).
SELECT reports . * , domains.name AS dom_name, publisher.pub_name
FROM reports, domains, publisher
WHERE reports.domain_id = domains.dom_id
AND publisher.pub_id = reports.pub_id
AND reports.rep_type =  'N'
AND reports.pub_id
IN ( 3 )
AND reports.rep_status =  'Y'
ORDER BY reports.rep_pub_date DESC
LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.
Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)
How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?


texts:
After installing mysql I get the following errors:

> 120105  6:02:49 [Warning] Setting lower_case_table_names=2 because
> file system for /usr/local/var/mysql/ is case insensitive mysqld:
> Table 'mysql.plugin' doesn't exist
> **120105  6:02:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.** 120105  6:02:49 InnoDB: The InnoDB
> memory heap is disabled 120105  6:02:49 InnoDB: Mutexes and rw_locks
> use GCC atomic builtins 120105  6:02:49 InnoDB: Compressed tables use
> zlib 1.2.5 120105  6:02:49 InnoDB: Initializing buffer pool, size =
> 128.0M 120105  6:02:49 InnoDB: Completed initialization of buffer pool 120105  6:02:49 InnoDB: highest supported file format is Barracuda.
> 120105  6:02:49  InnoDB: Waiting for the background threads to start
> 120105  6:02:50 InnoDB: 1.1.8 started; log sequence number 1595675
> **120105  6:02:50 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist**

I have tried to run mysql_upgrade and I get the following:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
FATAL ERROR: Upgrade failed

I have tried to re-install mysql. I get the same results.

I am wondering if someone can suggest some useful tricks to get this working.  I am using a mac running 10.7.2

Thanks again for your help in advance.


texts: How recording of every change of a row in a database is generaly stored?

texts:
In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address and when, and be able to restore previous version.

The similar thing is used for example by Stack Exchange. When I change someone else's question, it is possible to find that I changed it, and to rollback the changes.

What is the general technique used to store every change to an object in a database, given that my current schema has mostly the same properties (below) that an average business app?

The objects have a relatively small size: there might be some nvarchar(1000) for example, but not huge blobs of binary data, this one being stored directly on disk, and accessed directly, and not through Microsoft SQL filestream,
The database load is pretty low and the whole database is handled by one virtual machine on a server,
The access to the previous versions doesn't have to be as fast as the access to the latest version, but still has to be up-to-date¹ and not too slow².

<tl-dr>

I thought about the following cases, but I have no real experience with those sorts of scenarios, so I would hear other's opinions:

Store everything in the same table, distinguishing the rows by ID and version. IMO, it is seriously stupid, and will hurt soon or later on performance level. With this approach, it is also impossible to set a different security level to the latest items and to the versions trace. Finally, every query would be more complicated to write. Actually, to access the up-to-date data, I would be forced to group everything by ID and retrieve, in each group, the last version.
Store the latest version in one table, and, at every change, copy the obsolete version to another table in another schema. The flaw is that every time, we store every value, even if it didn't change. Setting unchanged values to null is not a solution, since I must also track when the value is changed to null or from null.
Store the latest version in one table, and the list of changed properties with their previous values in another table. This seems having two flaws: the most important one is that the only way to sort heterogeneous types of previous values in the same column is to have a binary(max). The second one is that it would be, I believe, more difficult to use such structure when displaying the previous versions to the user.
Do the same thing as in two previous points, but store the versions in a separate database. Performance-wise, it might be interesting in order to avoid slowing down the access to the latest versions by having the previous versions in the same database; still, I believe that it's a premature optimization and must be done only if there is a proof that having older and latest versions in the same database is a bottleneck.

</tl-dr>

¹ For example, it would be unacceptable to store the changes into a log file, as it is done for HTTP logs, and flush the data from the log to the database at night when the server load is lowest. The information about different versions must be available immediately or nearly immediately; a few seconds delay is acceptable.

² The information is not accessed very frequently and only by specific group of users, but still, it would be unacceptable to force them to wait for 30 seconds for the list of versions to display. Again, a few seconds delay is acceptable.


texts: Stored procedure - problem with merge - Insert

texts:
I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct?

Any help is appreciated.

Here is the procedure:

CREATE TYPE TrackType AS TABLE
(
t_Id uniqueidentifier,  t_JobID  nvarchar(50), t_CreatedDate datetime2(7),
t_ModifiedDate datetime2(7), t_DbDate    datetime2(7)
t_SubGUID nvarchar(MAX), t_eType nvarchar(MAX)
);
GO

ALTER PROCEDURE [dbo].[Process_NO_table]
@track       TrackType READONLY
AS
// i need to iterate all the rows of the table(over a million)

Declare @rows INT
Declare @i int = 0
Declare @count int = (SELECT COUNT(*) FROM @track)
DECLARE @is INT

  while (@i < @count)
BEGIN

// first i check modified date from the database table
   SELECT  @is = COUNT(*) FROM NO_table WHERE [JobID] IN (SELECT [t_JobID] FROM
@track)
MERGE [dbo].[NO_table] AS [Target]
USING @track AS [Source]

// if the database modifed date is less than the modifeid date from the
proceduretable(@track) then it updates the records
ON [Target].[ModifiedDate] < [Source].[t_ModifiedDate] AND JobID = t_JobID
WHEN MATCHED THEN
    UPDATE  SET [JobID] = [Source].[t_JobID],
        [CreatedDate] = [Source].[t_CreatedDate]
  [DbDate]= [Source].[t_DbDate]
 [ModifiedDate] = [Source].[t_ModifiedDate]
[SubGUID] = [Source].[t_SubGUID]
 [eType] = [Source].[t_eType]

// if there no records with the same jobid then it creates-insert a records
MERGE [dbo].[NO_table] AS [Target]
USING @track AS [Source]
ON (@is != 0)
WHEN NOT MATCHED THEN
   INSERT INTO [NO_table] ( [JobID], [CreatedDate], [ModifiedDate], [DbDate],
[SubGUID], [eType]  )
VALUES ( [Source].[t_JobID], [Source].[t_CreatedDate], [Source].[t_ModifiedDate],
[Source].[t_DbDate], [Source].[t_SubGUID], [Source].[t_eType] )
;

SET @i = @i + 1

END

GO


texts: cannot generate sspi - None of Solution worked!

texts: I am receiving "cannot generate sspi" error when I try to connect a sql instance remotely. I suspected the issue might be with spn and verified that spn was registered properly on the service account(confirmed the same with AD team as well). Also tried to get the server removed from domain and rejoined. The issue still persists and issue started occurring all of sudden and there were no changes on the server (patching, reboot). Would appreciate if anyone can provide some lead to resolve this issue?


texts: Soft delete: Copy row data to archive table when deleting

texts:
How can I move data from my current table to an archive table when a delete is called on the table?

My current table and archive table needs to be in sync in their schema too.

Can someone help me?

Edit:
I am using MySql


texts: What is the proper way to backup MySQL database with rsnapshot?

texts:
I want to backup my website that contains both user uploaded images and data stored in MySQL database such that they are always consistent with each other in any particular point of time. While searching for solutions, I found this application rsnapshot which might be appropriate for the task. Having gone through a few blogs on the internet, I realize that this is actually done with a backup_script using mysqldump method:

backup_script   ssh root@example.com "mysqldump -A > /var/db/dump/mysql.sql"    unused2
backup  root@example.com:/var/db/dump/  example.com/

What I am not sure is whether there is a necessity to flush all tables with read lock before performing the dump in the script to ensure consistency of the database. If so, how should this be incorporated? If no, why is it not necessary?


texts: MySQL: Limit number of rows returned with a given column value

texts:
My table has a list of activities, each associated with a customer and each with a date when the activity happened. I want to group by the customer ID and the month and the year, then sort by the month, the year and the count to generate a list of time activity generators by month.

SELECT
    `original_actor_id`,
    MONTH(activities.`created_at`) as month,
    YEAR(activities.`created_at`) as year,
    COUNT(*) as count
FROM `activities` activities
WHERE `activity_type` = 'product_video_completed'
GROUP BY
    `original_actor_id`,
    MONTH(activities.`created_at`),
    YEAR(activities.`created_at`)
ORDER BY
    YEAR(activities.`created_at`) DESC,
    MONTH(activities.`created_at`) DESC,
    count DESC

However, I am only interested in the top 5 counts for each month/year combo. What is the best way to limit this to the top 5 higest counts for each month/year combo?


texts:
We normally perform backups across the network to a file share on a different SAN.  This works very well for all but 1 of our database servers. The problem machine is a new server running Windows Server 2012R2 as the OS, and SQL Server 2012 Standard Edition for the database instance.  The network latency between the server and backup share is sub-MS.

The actual backup of the data and log file happen quickly (sub-second), however, it seems to get stuck at 100% completed (when viewed through sp_WhoIsActive) with a BACKUPTHREAD wait type.  If I perform the same backup but to a local drive, it does not experience this problem.

Here is a sample backup command we are running for the MODEL (only 5MBs) database:

USE MASTER
GO

BACKUP DATABASE MODEL
TO  DISK = N'\\<FileShareServer>\SQL_Backup\MODEL.BAK'
WITH  NAME = N'MODEL-Full Database Backup'
    ,NOFORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    ,CHECKSUM, COPY_ONLY, COMPRESSION;

GO

Here are the stats for the backup, with Trace Flag 3604, 3213 turned on:

Backup/Restore buffer configuration parameters

Memory limit: 36855MB

BufferCount:                7

Sets Of Buffers:            3

MaxTransferSize:            64 KB

Min MaxTransferSize:        64 KB

Total buffer space:         1 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    0

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:            7

Media Buffer size:            64KB

Encode Buffer count:           7

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
51 percent processed.
61 percent processed.
71 percent processed.
81 percent processed.
91 percent processed.

Processed 312 pages for database 'MODEL', file 'modeldev' on file 1.
100 percent processed.
Processed 2 pages for database 'MODEL', file 'modellog' on file 1.
-- Up to here happens sub-second as expected
BACKUP DATABASE successfully processed 314 pages in 252.372 seconds (0.009 MB/sec).

I've tried playing around with stripped backups as well as setting the BUFFERCOUNT and MAXTRANSFERSIZE to higher numbers, to no avail.

Thanks in advanced for your input!

Based on Aaron Bertrand's suggestion, I turned on trace flags 3605, 3004, and 3014 and got the following additional information (only pasting in the portion from where it gets to 100% and then sits waiting):

慂正灵慄慴慢敳退邐邐邐邐坖呁啁荈⃬튅襌⑴䱘粉怤ཁ쒔譌䣹솁̘ : Log files done

Backup: Trailing config done

Backup: MBC done

BackupDatabase: Writing history records

Writing backup history records

BACKUP DATABASE successfully processed 314 pages in 251.106 seconds (0.009 MB/sec).
BackupDatabase: Finished

Just to clarify the entries above, starting with, what I'm guessing are Chinese characters, only display once the backup completes.  However, looking in the log file, up to the "Backup: MBC done" entry has the same timestamp as all the previous entries.  "BackupDatabase: Writing history records" has a timestamp 4 minutes later.


texts: Why is a scan faster than seek for this predicate?

texts:
I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals.

On my machine, the following query does a clustered index scan and takes about 6.8 seconds of CPU time:

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

The following query does a clustered index seek (only difference is removing the FORCESCAN hint) but takes about 18.2 seconds of CPU time:

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

The query plans are pretty similar. For both queries there are 120000001 rows read from the clustered index:

I am on SQL Server 2017 CU 10. Here is code to create and populate the two_col_key_test table:

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

I am hoping for an answer that does more than call stack reporting. For example, I can see that sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataX takes significantly more CPU cycles in the slow query compared to the fast one:

Instead of stopping there, I'd like to understand what that is and why there's such a large difference between the two queries.

Why is there a large difference in CPU time for these two queries?


texts: How to know if SQLite3 is still running or it is crash?

texts:
I have an SQL command that insert data into a table by joining data from several tables. The command started working at 13:25:05 on 2018-01-14. And now, 15:56 on 2018-01-15, it's still not yet finished. I checked if there are any change with the database file but my database file production.db and the journal file production.db-journal have no update since yesterday as below

4608 Jan 14 13:25 production.db-journal

135711874048 Jan 14 17:41 production.db

Similar SQL statement was done before with the same target table but different number of joined tables. Last time, it took around 3 hours for the operation. And this time I expected it to take around 4 hours. But now, it's almost a day.

Does anyone know how to see if SQLite is still working or it's already crash?


texts: Error when sending email with attachment using SQL Server login

texts:
I'm trying to send email using msdb.dbo.sp_send_dbmail  by a SQL Server login.

It works fine if I'm not attaching anything. But if I attach anything I get the following error:

"The client connection security context could not be impersonated. Attaching files require an integrated client login"

so I mapped that SQL login to a credential that is a local Windows account that I'm sure it has full access to the file I want to attach, as follows:

however, I'm still getting the same error.

What is wrong? am I missing something? how can I fix it? how can I allow non-sysadmin SQL Server logins to send emails with attachment?

Note that:

The file is local and not on a network drive
I have Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)   Mar 19 2015 12:32:14   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Update:

I have also tried to wrap sp_send_dbmail into a stored procedure the EXECUTE AS the windows account which has all the permission on the file, it can execute the wrapping stored procedure, and it can execute successfully sp_send_dbmail with attachement ... I've tried something like:

EXECUTE AS LOGIN='MyMachine\SQLMailUser'

 EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLMail',
    @recipients = 'myEmail@myDomain.xyz',
    @body ='some text in email body',
    @subject = 'some text for the subject',
    @file_attachments='D:\test\test.txt',
    @body_format ='HTML';

but same error :(


texts: Postgresql SUM query giving "cannot enlarge string buffer"

texts:
Postgres version is 11.2, dockerized, the default image with pg_activity installed as an extra.

OS is Ubuntu server 18.04. Base dockerfile is debian:stretch-slim

Call is done via the PostgreSQL JDBC Driver, org.postgresql:postgresql:42.2.5

I just now encountered a simple SUM() query giving me this:

  Cannot enlarge string buffer containing 0 bytes by 1342170831 more bytes.

The query is something like:

select sum(size) from table;

The "size" field being bigint.

I wouldn't expect such a query to give me this problem. What's this indicative of? Are certain "size" fields beyond all proportion? I'd think the biggest not have more than 12 digits.

This has me confused. What should I check for?

Also, is there a more efficient way to perform this query, as to not trigger that 1 big stringbuffer enlarge?

EDIT:

Result of SELECT MAX(size), MIN(size) FROM table;

     max      | min
--------------+-----
 230780611421 |   0
(1 row)

So yeah, 12 digits max, as I expected.

Amount of items in the table: 4123633

Quick calculation: 1342170831 / 4123633 = 325.48

I won't be able to isolate for a test case right away. Nor will I be able to quickly upgrade the install.

EDIT2: It does not happen when doing the query via PSQL. Casting to numeric does not make a difference, both work via psql.

The result is 75559012258007 btw.

EDIT3: I can't output the entire table, so here's just the field:

               Column               |            Type             | Collation | Nullable |                   Default
------------------------------------+-----------------------------+-----------+----------+----------------------------------------------
 size                               | bigint                      |           | not null |

And yes, it is a table, not a view.


texts: Will moving the MATCH...AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance?

texts:
Basically, I have the following query:

select `t`.*
from `tracking` `t`
where exists
(
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`) against('+bulgaria +turkey' in boolean mode)
    limit 1
) and ... limit 5

The problem I have?

There's FULLTEXT index for c.car_text column, but however the query is running a bit slowly (especially for records that rarely occurs, e.g. searching for non-existing keyword).

I haven't published the result of EXPLAIN, as I'd like to know the theory behind my question, e.g. for different possible cases.

I use MySQL 5.6 with InnoDB storage engine, also it's worth to mention that cars table have like ~160m records.


texts: (How) can I return the most specific row on a join?

texts:
I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For example, consider a teddy bear store. The default teddy bear price is $20. If, however, the teddy bear is at least 12 inches tall, we want to charge $30. If the teddy bear is made of suede, we charge $40, and if the teddy bear is both at least 12 inches tall and made of suede, we charge $60 (because the larger bear also requires more of the expensive fabric). ETA: We now also have a grass bear special because we just hired a teddy bear maker who was previously a florist. Given the perishable nature of the bear, it costs $15 if it's at least 24 inches tall.

Here's what the prices table I have looks like (sorry about the formatting):

EDITED: I added the grass bear to better reflect my inability to make any assumptions about the data. I simply need to pick the price that best corresponds to each product without knowledge of how different features affect the price, positively or negatively.

  price | min_ht | fabric
---------------------------
| 20    | NULL   | NULL   |
---------------------------
| 30    | 12     | NULL   |
---------------------------
| 40    | NULL   | suede  |
---------------------------
| 60    | 12     | suede  |
---------------------------
| 15    | 24     | grass  | <-- our floral special
---------------------------

which I want to join with a products table like

  id    | height | fabric
---------------------------
| 1     | 8      | cotton |
---------------------------
| 2     | 6      | poly   |
---------------------------
| 3     | 4      | suede  |
---------------------------
| 4     | 18     | cotton |
---------------------------
| 5     | 12     | suede  |
---------------------------
| 6     | 30     | grass  |
---------------------------

to yield

  id    | height | fabric | price
-----------------------------------
| 1     | 8      | cotton | 20    |
-----------------------------------
| 2     | 6      | poly   | 20    |
-----------------------------------
| 3     | 4      | suede  | 40    |
-----------------------------------
| 4     | 18     | cotton | 30    |
-----------------------------------
| 5     | 12     | suede  | 60    |
-----------------------------------
| 6     | 30     | grass  | 15    |
-----------------------------------

Any ideas? I saw a similar question, though it is unfortunately both unanswered and regarding MySQL.


texts:
I'm trying to execute a query across around 200 servers using openrowset function.
This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in older ones.
I'll illustrate this issue with a simple query:

    SELECT CAST(a.ServerName AS varchar(50)), CAST(a.LoginName AS varchar(50))
FROM OPENROWSET('SQLNCLI', 'Server=2012ServerName;Trusted_Connection=yes;','
SET NOCOUNT ON
DECLARE @LoginName sysname
DECLARE @FinalResults TABLE (LoginName varchar(50))
DECLARE LoginsCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT sl.Name LoginName
FROM master.dbo.syslogins sl WITH (NOLOCK)
OPEN LoginsCursor
FETCH NEXT FROM LoginsCursor INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @FinalResults
SELECT @LoginName
FETCH NEXT FROM LoginsCursor INTO @LoginName
END
CLOSE LoginsCursor
DEALLOCATE LoginsCursor
SELECT SERVERPROPERTY(''ServerName'') ServerName , LoginName FROM @FinalResults
') AS a

Result is success, but if I'll change provider to SQLNCLI10(I'm running this query from SQL Server 2012)
It will fail. Also if I'll change server name to server with previous SQL version this query will fail.
Is there an update in native client 11 that makes this query sucessful and is there a way to overcome this problem?


texts: What are the pros and cons of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE

texts:
I have multiple questions

For Oracle Database 11g Release 11.2.0.1.0

what are the benefits of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE.
How will the CLIENT side cache be kept sync if data changes at server side.

Do we have a AUTO mode for RESULT_CACHE_MODE? What is the recommended mode for this?

If i execute a single query 10 times with CLIENT_RESULT_CACHE enabled will the query be run 10 times at the server?

Please point me towards any documentation regarding this.Thanks in advance


texts:
Is there a script that I can run to get an email when someone else other than me logs in as root?

I'm new to MySQL. These are my contents of my.cnf:

[mysqld]
*Remove leading # and set to the amount of RAM for the most important data
cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

*innodb_buffer_pool_size = 128M

*Remove leading # to turn on a very important data integrity option: logging

*changes to the binary log between backups.

*log_bin

*Remove leading # to set options mainly useful for reporting servers.

*The server defaults are faster for transactions and fast SELECTs.

*Adjust sizes as needed, experiment to find the optimal values.

*join_buffer_size = 128M

*sort_buffer_size = 2M

*read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

*Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

port=1433

Please suggest a way to get an email alert if someone logs in as root into MySQL.


texts: Help wrapping my mind around many-to-many relationships

texts:
I'm making a database that needs to have the following tables: Article, Video, Image, and Keyword. Keyword contains a fixed list of keywords. (For example when creating an article, you'd have to select from a list of keywords, not create a new one) Each Article, Image, and Video can have n number of keywords. I'm having a hard time wrapping my mind around how I'd define these relationships in my database, as I've really only dealt with one-to-one and one-to-many in the applications I've had to develop in the past. Would I do a many-to-many between Keyword and the other tables? Or would I need to make a join table between each table? KeywordImage (keyword_id, image_id)? Or are neither of those options the right way to think about this?

If a join table is the right approach, would it be one-to-many between Article and ArticleKeyword?

edit
Keyword isn't a required field in any of the tables


texts: Postgres- update a value in a array on particular index

texts:
I have a column named ids that is of type bigint[]. ids cane be null or of any length

It has values based on certain hierarchy.

My use case is I want to update value that is at a particular index.
For example ids[2] = 50.

How can I achieve this?

I looked at this https://www.postgresql.org/docs/current/functions-array.html#ARRAY-FUNCTIONS-TABLE
but couldn't find anything that could help.

Thanks in advance.


texts: Is it possible to limit the scope of a SYNONYM in SQL Server?

texts:
I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table member which will always have the columns member_number, name, and dob. The stored procedure find_new_members() would use exactly the same SQL no matter which database it was executed against.

I've found the command CREATE SYNONYM which looks like it will do the trick if I do something like this:

DECLARE @database NVARCHAR(16) = 'ClientA';
DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database;
EXECUTE sp_executesql @statement;
// Run generic statements

This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts.

Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM CurrentDB?

EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.


texts: Counters to distinguish Insert Hotspot / Page Split by seeing PAGELATCH_EX

texts:
Insert hotspot and excessive page splits are common reasons for PAGELATCH_EX.

What are the other counters that can be used to confirm whether the wait is from insert hotspot or page split?

Note: I am using SQL Server 2012.

REFERENCES

sys.dm_os_wait_stats


texts: How to find out all the foreign key reference tables for a table in Sybase by writing a Query .


texts: MySQL: Fixing Unique key constraint violation

texts:
I have a database with an unique key constraint.

When I import this database from a dump created by mysqldump I get

'14698-3' for key 'UNQ_CATALOGS EARCH_FULLTEXT_PRODUCT_ID_STORE_ID'

But when I check the database where the export was generated, the constraint violation is not visible:

mysql> select count(*) from catalogsearch_fulltext where product_id = 14698 and store_id = 3;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

I suppose that MySQL does not show the second entry, because it uses the index.

How can I show this entry and fix it easily on the live system?


texts:
We are experiencing huge logs creation in our MongoDB shard setup. Log settings used are default values -

            firstset:PRIMARY> db.getLogComponents()
  {
"verbosity" : 0,
"accessControl" : {
    "verbosity" : -1
},
"command" : {
    "verbosity" : -1
},
"control" : {
    "verbosity" : -1
},
"executor" : {
    "verbosity" : -1
},
"geo" : {
    "verbosity" : -1
},
"index" : {
    "verbosity" : -1
},
"network" : {
    "verbosity" : -1,
    "asio" : {
        "verbosity" : -1
    },
    "bridge" : {
        "verbosity" : -1
    }
},
"query" : {
    "verbosity" : -1
},
"replication" : {
    "verbosity" : -1
},
"sharding" : {
    "verbosity" : -1
},
"storage" : {
    "verbosity" : -1,
    "journal" : {
        "verbosity" : -1
    }
},
"write" : {
    "verbosity" : -1
},
"ftdc" : {
    "verbosity" : -1
}

}

We need only error and warning logs. Reference document (https://docs.mongodb.com/manual/reference/log-messages/) mentions these levels. I need to know how i can change the level.


texts:
I've been trying to install Apache because I want to do PHP development work.

The install didn't work so I aborted it and wanted to make sure that my other installs using MySQL were still working.

I can no longer seem to connect to my SQL server though.

HeidiSQL returns:

SQL Error (2003): Can't connect to MySQL server on '127.0.0.1' (10061)

Ruby on rails on Server start up returns:

Can't connect to MySQL server on 'localhost' (10061)

MySQL Command Line Client returns:

Error 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I've tried the suggestions found in this thread:
https://stackoverflow.com/questions/385292/mysql-cant-connect-to-mysql-server-on-localhost-10061

and have come with the following results:

telnet localhost 3306
Connecting to localhost... Could not open connection to host, on port 3306: Connect failed

sc query mysql
[SC] EnumQueryServiceStatus:OpenService Failed 1060:
The specified service does not exist as an installed service.

Yet when I look in SQL Server Configuration manager, it says SQL Server (SQLExpress) is in a running State.

I can't seem to troubleshoot this issue. Please help!


texts: Splitting SQL Server tempdb to use a second drive when first drive is full

texts: My SQL Server instance has a small 32 GB SSD which is mostly unusedw while the remaining drives are SAN storage. I considered moving my tempdb to the SSD but my fear is the odd occasion when the tempdb exceeds the SSD size will cause the query to stop. I've read that multiple files will allow the tempdb to split usage across drives but is there a way to put a preference on the SSD drive until full in which case it will resume on the SAN storage?


texts: Duplicate error after truncation for Innodb tables ( 5.7.17 - Enterprise edition )

texts:
I have seen a strange behavior in MySQL Innodb tables having below structure :

CREATE TABLE `bug` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`num` int(11) DEFAULT NULL,
`src` varchar(16) NOT NULL,
`destination` varchar(30) DEFAULT NULL,
`Text` varchar(2000) DEFAULT NULL,
`status` varchar(50) NOT NULL,
`response` varchar(50) NOT NULL DEFAULT 'PNDNG',
 PRIMARY KEY (`Id`),
KEY `idx_status_num` (`status`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

I have truncated this table :

truncate table bug;

After this , application started inserting records in it , and then i got duplicacy errors on primary key Id which was very strange for me. Moreover , i haven't seen any error in error log file related to this.

After this , i dropped and re-created this table :

drop table bug;

After this , application started inserting records in it and everything went fine .

So what was wrong ? Did Innodb table got corrupted ? If so , then how can I identify if table is corrupted ?  As far as i know, it's MyISAM table which usually get corrupt.

Index on this is below :

       mysql> show index from bug\G;
      *************************** 1. row ***************************
      Table: bug
      Non_unique: 0
      Key_name: PRIMARY
      Seq_in_index: 1
      Column_name: Id
      Collation: A
      Cardinality: 0
      Sub_part: NULL
      Packed: NULL
      Null:
      Index_type: BTREE
      Comment:
      Index_comment:
 *************************** 2. row ***************************
      Table: bug
      Non_unique: 1
      Key_name: idx_status_num
      Seq_in_index: 1
      Column_name: status
      Collation: A
      Cardinality: 0
      Sub_part: NULL
      Packed: NULL
      Null:
      Index_type: BTREE
      Comment:
      Index_comment:
     *************************** 3. row ***************************
      Table: bug
      Non_unique: 1
      Key_name: idx_status_num
      Seq_in_index: 2
      Column_name: num
      Collation: A
      Cardinality: 0
      Sub_part: NULL
      Packed: NULL
      Null: YES
     Index_type: BTREE
     Comment:
   Index_comment:
   3 rows in set (0.00 sec)


texts:
On an empty transient table, inserting a single row took 50ms before changing it to UNLOGGED, 1ms afterwards.

Why is this exactly?

If the table structure is necessary, I'll post it, but it's only five columns that normally total 500 bytes per row, a composite primary, and a composite unique.

If possible, can the performance cost be pinpointed to calculation or disk interaction?  If so, what percentage of the cost is due to each?


texts: Data Migration or Sharded Cluster to new Cluster

texts:
We have Sharded Mongo Cluster. However some documents are sharded and others are unsharded.

We are planning to migrate these databases to new cluster. Please update if there is any process or steps to migrate the data from one sharded cluster to the other.

We have two secondaries and 1 primary for each shard,MongoS,3 Config Servers,2 Arbitors. I understood that copydb can not be used on the sharded collection. Please suggest if there is any other way.


texts: Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

texts:
We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4.

After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad at one point that the decision was made to revert back to the old cardinality estimator by enabling the 9481 traceflag and UPDATEing statistics again. When I say "got so bad" I'm referring to the execution times for queries increasing by a magnitude of 10 in some cases.

Using traceflag 9481 has resolved the problem but this can't be the solution can it?

Searching Google has shown some taking the old cardinality estimator route and others using a combination of 2312 and 4199 to use the new estimator.

So after upgrading from 2008R2 to 2014, what combination of traceflags (if any) and other steps should we be taking?

Thanks,
Craig

Update April 26th 9am

The 4199 traceflag does not turn on the new Cardinality Estimator. I had to use the 2312 traceflag instead.

With the 4199 traceflag, the version was still on 70. The answer from Chris Wood reminded me of an article from Brent Ozar I'd also read at some point. Still waiting to see if the execution times improve.


texts: Change field values to maintain a difference of 0.1

texts:
So I have a table like this in MS SQL Server 2014:
ID| From  | To   | Diff |Tenths
1 |19.023 |19.121|0.098 |0
1 |19.121 |19.220|0.099 |1
1 |19.220 |19.321|0.101 |2
1 |19.321 |19.419|0.098 |3
1 |19.419 |19.522|0.103 |4
1 |19.522 |19.622|0.1   |5
1 |19.622 |19.722|0.1   |6
1 |19.722 |19.723|0.01  |7

I am trying to do this:
Per ID-Ensure the difference is 0.1 for all tenths unless the diff is <=0.01 in which case I want it to be added to the previous entry.
The resulting set would look something like this:
ID| From  | To   | Diff|Tenths
1 |19.023 |19.123|0.1  |0
1 |19.123 |19.223|0.1  |1
1 |19.223 |19.323|0.1  |2
1 |19.323 |19.423|0.1  |3
1 |19.423 |19.523|0.1  |4
1 |19.523 |19.623|0.1  |5
1 |19.623 |19.724|0.101|6

I tried using CASE statement like this but it only gave me the first 2 rows, stuck in trying to figure out how to make it to work for the entire ID
select *,
CASE when tenths=0 then NFrom else lag(Nfrom) over (partition by ID order by ID,tenths,From)+0.1 end Nfrom2,
CASE WHEN tenths=0 then Nto+0.1 else lag(nto) over (partition by ID order by ID,tenths,from)+0.1 end Nto2
from
(
   select *,
   CASE WHEN tenths=0 then From else Null end NFrom,
   CASE when tenths=0 then from+0.1 else NULL end NTo
From testtable
)x

Is there any way to do this?


texts: Primary Key At the End of Foreign Key index in MySQL is in order for sure?

texts:
I am aware that any Index in MySQL Innodb contains primary key value in the end of it. If there was a table as follows.

create table BaseTbl (
    base_id int not null primary key auto_increment,
    base_name varchar(30)
);

create table Tbl (
    record_id int not null primary key auto_increment,
    base_id int not null,
    foreign key (base_id) references BaseTbl(base_id)
);

Table Tbl would have an index on base_id because that is FK. And that would implicitly have record_id as the final key part. Would that be ordered?

Example:

If the data was

BaseTbl
+---------+-----------+
| base_id | base_name |
+---------+-----------+
|       1 | Name1     |
|       2 | Name2     |
|       3 | Name3     |
+---------+-----------+

Tbl
+-----------+---------+
| record_id | base_id |
+-----------+---------+
|         1 |       1 |
|         2 |       2 |
|         3 |       1 |
|         4 |       1 |
|         5 |       1 |
|         6 |       2 |
|         7 |       2 |
|         8 |       1 |
|         9 |       3 |
|        10 |       1 |
|        11 |       2 |
|        12 |       3 |
+-----------+---------+

Would the base_id FK index's record_id part will it be ordered? Like this?

+---------+-----------+
| base_id | record_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       1 |         3 |
|       1 |         4 |
|       1 |         5 |
|       2 |         6 |
|       2 |         7 |
|       2 |         8 |
|       3 |         9 |
|       3 |        10 |
|       3 |        11 |
|       3 |        12 |
|       3 |        13 |
|       3 |        14 |
|       3 |        15 |
+---------+-----------+

It seems like it is ordered, as I did not see 'using FileSort' when I explicitly tried mentioning Order By on record_id. But I'd like a confirmation or point in documentation that confirms this. (I failed to find it myself).