texts: update stored proc ony fields that are passed to the proc and leaving others alone

texts:
i have a  stored proc  that does a simple update (see below).

i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want to update the table data to null, but rather just leave what the current value is.

can this be done in a sql update statement such as this?

ALTER PROCEDURE [dbo].[upd_MessageDetail]
    @MessageId INT,
    @IsDraft INT,
    @IsPreviewed INT,
    @IsRead INT,
    @IsFlagged INT,
    @IsDeleted INT,
    @IsArchived INT
AS
BEGIN

        UPDATE MessageDetail
        SET
            IsDraft = @IsDraft,
            IsPreviewed = @IsPreviewed,
            IsRead = @IsRead,
            IsFlagged = @IsFlagged,
            IsDeleted = @IsDeleted,
            IsArchived = @IsArchived
        WHERE MessageId = @MessageId


texts:
I had a SQL Server 2000 MDF on a SQL Server 2005 server (moved from prior version), there was a hardware failure.

After sending harddisk, it was supposed the mdf / ldf files were recovered, but I am unable to attach.

Something curious is that the MDF file has a date on disk from june 2010 and the problem happened in sept 2010. I have tried several options regarding making a new fake db, replace files, mdf, emergency etc, but with no results, can some one help to see how can this be fixed?

  Attach database failed for Server 'SERVERDATA'.  (Microsoft.SqlServer.Smo)

  SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x55555556). It occurred during a read of page (1:24) in database ID 7 at offset 0x00000000030000 in file 'C:\sqldata\ipvtelas_Data.MDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  Could not open new database 'ipvtelas'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

After making a dummy database and replacing the MDF/LDF files, the error is now:

  Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Database 'dummy' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)


texts: Precautions for moving a SQL cluster from an old to a new datastore

texts: My storage team  wants to move a SQL cluster (2 node VM) from an old data store to Storage vMotion . What are the precautions I can take before this exercise is performed? I am planning to take database snapshots before the exercise and then shutdown the cluster and databases. The RDM's will be reconnected once the VM's have been moved to the new datastore. Are there any other best practices which I can follow before this process? Please advice.


texts: mysql cluster with innodb utilizing high memory

texts:
I have implemented MySQL cluster with two data, sql and management node respectively.
On one of the sql node I have ndb as well innodb engine for the huge tables which I can not able to convert into ndb-cluster engine. But after few days the sql node with innodb engine started eating large amount of physical memory it is increasing rapidly. Show processlist is not showing any running processes. I am clueless now where is MySQL consuming this RAM. I am worried if my server goes out of memory. One week ago I have uploaded 30GB of data but right now it is not used by any of the application. I have observed increase In ram usage goes 20% to 75% within a week after that data upload.

[mysqld]

ndbcluster
ndb-connectstring=192.168.0.105,192.168.0.109   # IP address of Management
ndb-cluster-connection-pool=2

[mysql_cluster]

ndb-connectstring=192.168.0.105,192.168.0.109   # IP address of Management Node

[mysqld]

ndbcluster
ndb-connectstring=192.168.0.105,192.168.0.109       # IP address for server management node
default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL

[mysql_cluster]

ndb-connectstring=192.168.0.105,192.168.0.109       # IP address for server management node
 #[mysqld_safe]
 #log-error=/var/log/mysqld.log
 #pid-file=/var/run/mysqld/mysqld.pid

 #log_bin=/var/log/mysql/mysql-bin.log
 #binlog_do_db=radius

 #port = 8881
 #innodb_force_recovery=6
 #innodb_purge_threads=0


texts: Oracle DB occasionally slows down when "Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN"

texts:
After midnight Oracle Database 12c turned to "slow mode". DML statements became about 10 times slower, though "selects" feel good. CPU load as usual, disk I/O is perfect (tested by copying 500m file), there are lots of free RAM and free disk space.

Restart of Oracle didn't help. All of the user sessions were killed if there were any.

In about one hour "slow mode" went away. Everything became as fast as before.

I've seen similar behavior on 11g as well.

Logs show:

    Thu Aug 07 00:38:13 2014
    Setting Resource Manager plan SCHEDULER[0x420E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
    Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

and when it's gone I see:

    Thu Aug 07 02:00:03 2014
    Closing scheduler window
    Closing Resource Manager plan via scheduler window
    Clearing Resource Manager plan via parameter

Nothing else that can be related to the above bug/feature I can see in the logs.

This trouble happens time after time.

How to avoid this?


texts: How to reproduce "Could not continue scan with NOLOCK due to data movement"

texts:
I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries.

I understand this has something to do with attempting to select data when there has been a page split which caused the data to no longer be where it was supposed to be - I assume that's what's happening in my environment.

How would I reproduce this?

I'm trying to do a short-term workaround to catch the error and retry when this happens, but I can't test it if I can't reproduce it. Is there a reasonably reliable way to cause this?

When it does happen, executing the query again results in success - so I don't really have any concerns about the actual data or database being permanently corrupt. Some of the tables in the query (along with their indexes) get dropped, recreated, and repopulated often, so I'm assuming it's something related to that.

Removing NOLOCK is my long-term issue to deal with. The reason NOLOCK was put there in the first place was that the queries are so bad that they were deadlocking with day-to-day transactions, so NOLOCK was a band-aid to stop the deadlocks (which worked). So I need a band-aid on a band-aid until we can do a permanent solution.

If I could reproduce it with a Hello World, I would plan on probably slapping the band-aid into the job in less than an hour. Can't do a search-and-replace removing NOLOCK, because I'd start getting the app deadlocks again, which are worse for me than an occasional failing job.

Using read committed snapshot isolation is a good possibility - I'll have to work with our database team to get more details about that. Part of our problem is that we don't have a SQL Server expert to deal with that kind of thing, and I don't understand the isolation levels well enough to make that change right now.


texts: Spatialite: use of virtual spatial index for distances

texts:
I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/pp-adjacent.html

According to the tutorial, in order to find the pairs of populated places that their distance is < 1km we have to do this query

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
    populated_places AS pp2
    WHERE GeodesicLength(
      MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
      AND pp1.id <> pp2.id
    AND pp2.ROWID IN (
      SELECT pkid
      FROM idx_populated_places_geometry
      WHERE pkid MATCH RTreeDistWithin(
       ST_X(pp1.geometry),
       ST_Y(pp1.geometry), 0.02))
    ORDER BY 2;

Which throws an error because of no longer use of geocallback functions RTree***. I checked the update on this and this has been substituted by the use of virtual spatial index. We have to use a subquery of the form

    SELECT ROWID
    FROM SpatialIndex
    WHERE
       f_table_name = <table_name>
       AND search_frame = <some_geometry>

So i tested this

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
    populated_places AS pp2
    WHERE GeodesicLength(
      MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
      AND pp1.id <> pp2.id
    AND pp2.ROWID IN (
      SELECT ROWID
      FROM SpatialIndex
      WHERE
       f_table_name ='populated_places'
       AND search_frame = pp1.geometry)
    ORDER BY 2;

And the result set was awfully wrong

   PopulatedPlace #1    Distance (meters)   PopulatedPlace #2
              Ariano    0.000000             Ariano
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
              Ariano    0.000000             Ariano

Could please someone explain this behavior and/or give an example of proper use of the virtual spatial index in order to obtain distance queries?


texts: MySQL 5.7 InnoDB is extremely slow on spatial data compared to MySQL 5.6

texts:
I have a table area which contains ~300,000 rows. It is defined as follows:

CREATE TABLE `area` (
  `area_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `boundary` geometry NOT NULL,
  `name` varchar(255) NOT NULL,
  `area_type_id` bigint(20) NOT NULL,
  PRIMARY KEY (`area_id`),
  KEY `FK_7r1ppwayxh76te8c6jq3nrd71` (`area_type_id`),
  SPATIAL KEY `boundary` (`boundary`)
) ENGINE=InnoDB AUTO_INCREMENT=311998 DEFAULT CHARSET=utf8;

The execution times on MySQL 5.6.24 and the latest GA 5.7.9 vary greatly (two orders of magnitude) for the following query:

set @p = st_GeomFromText('POINT(14.822664 56.858916)');
select area_id from area where st_within(@p, boundary);

Although the result of the query ran on either system is the same, the execution times are not:

| MySQL 5.6.24-log (x86/Win32) | MySQL 5.7.9-log (x86_64/Win64) |
| ---------------------------- | ------------------------------ |
|                   0.0553 sec |                     8.5342 sec |

So, the newer MySQL version is 154 times slower. Am I doing something wrong?

The explain looks as follows:

| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra
| -- | ----------- | ----- | ---------- | ----- | ------------- | -------- | ------- | ---- | ---- | -------- | -----
| 1  | SIMPLE      | area  | NULL       | range | boundary      | boundary | 34      | NULL | 1    | 100.00   | Using where

Edit: Here is a dump of the table which I used (~821mb).


texts:
I have this PostgreSQL query where I have 2x $i in a bash-like notation to make it comprehensible that I want to print out a number i ranging from 0 to 20 at these positions using a for loop. Briefly, the idea is to generate some SQL code as a temp table that I can dump to a file. Which file is then executed to actually run these queries.
SELECT CONCAT(
    'CREATE MATERIALIZED VIEW IF NOT EXISTS "',
    table_name, '_factor', $i,
    '" AS SELECT id, POWER(2,', $i,') AS factor ',
    'FROM "', table_name, '" WHERE factor IS NOT NULL;'
)
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
  AND table_name NOT LIKE '%suffix$'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

Notice also that this SELECT statement already loops over N tables.
From what I've read so far, this would be the kind of structure in which to embed my code:
DO $$
BEGIN
    FOR i IN 0..20 LOOP
        -- insert the previous query here
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
END; $$

But when I do so and actually execute it, I got this error message:
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601

And if I replace SELECT by PERFORM, I end up with a single word "DO" dumped in my file.
How could I generate all my queries using a for loop?
Resulting queries should look like:
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;

Precisions: I store this SQL statement in a /sql_queries/generate_views_queries.sql file and execute it to dump the resulting queries in an other file /sql_queries/do_create_views.sql:
psql -d "host=${HOST} port=${PORT} dbname=${POSTGRES_DB} user=${POSTGRES_USER} password=${POSTGRES_PASSWORD}" \
  -f "/sql_queries/generate_views_queries.sql" \
  -t --output="/sql_queries/do_create_views.sql"

PG version is 13.1 from the official PostgreSQL Docker image.


texts: Is select case when null better than iif(exists)?

texts:
I try to reduce huge performance issues on our application database. We use SQL Server 2014 and we have a lot of views.

I wonder if there is a significant impact of rewriting left joins to use exists in projections ?

My question is: is there any best practices concerning performance when constructing queries that project different data depending on related matches existence ?

I mean,
which of these queries could be considered better ?

1) :

select
        a.Id,
        case when b.Id is not null
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a
left join B b on a.Id = b.Id;

2) :

select
        a.Id,
        iif(exists(select top 1 1 from B where Id = a.Id),
            convert(bit,1),
            convert(bit,0)
           )  IsActive
from A a;

By the way, if you have any comments on the form of the queries, or general advice, please feel free to comment.


texts: How do you use COLUMN_JSON with COLUMN_GET on dynamic columns in MariaDB?

texts:
When I do the following query;

SELECT name, COLUMN_GET(attr, 'appearance' as char) as 'attributes' from items;

I see the 2 columns including the blob data with the weird formatting.

Is it possible to include the COLUMN_JSON function to format the data inside of the select query?


texts: What language and data structures does MYSQL uses as backend?

texts:
I am just curious to know what data structures and languages does MYSQL exactly use as back end language? and what does the file format .MYD , .MYI and .frm stand for and how does MYSQL use them?

Thanks.


texts:
There is an application, it maintains delete flag column i.e. if a delete happen the column marked as 1 (in the deleted column).

To retrieve the record I must take this 1 to a NULL

There are 135 tables having the deleted column, and all of them must be updated based on ID column (when it has particular value)

( SELECT *
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'deleted')

How can I perform: UPDATE all tables containing the deleted column SET deleted = NULL WHERE ID = 'XXXX'


texts: Mysql warning: InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

texts:
Whenever my MySQL 5.7 starts I get the warning

InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

bar is a table I experimented with long time ago with import tablespace, but its long deleted and I don't see any files associated with it in the database directory.

drop table bar doesn't work.

How do I get ride of this warning ?


texts: How to avoid delimiter character in BCP export?

texts:
In my project, I need to use SQL 2008 BCP tool to export the result of a stored procedure into a fixed column width file. I used different approaches, but still couldn't get expected result. Things I tried are explained below.

(The database table from where the SP is fetching values is designed with all columns as varchar with width as needed by the specification of the requirement.)

First I tried (as per this site)

bcp "my_sample_sp" queryout "C:\table_output.txt" -S <servername> -d <dbname> -U <username> -P <password> -c -t \0 -a 10000 -L 100

This gave me the result with a single space between all columns as delimiter.
Unexpected. Dont need any delimiter.

Second, I tried

bcp "my_sample_sp" queryout "C:\table_output.txt" -S <servername> -d <dbname> -U <username> -P <password> -c -t \0x90 -a 10000 -L 100

This gave me the result with a hidden character between all columns as delimiter.
Unexpected. Most editors and the end application consuming this file read that invisible value as one character.

Third, I tried with format file in bcp tool. (followed this site)

Created native format file using below command

bcp "my_sample_Table" format nul -n -f C:\format_file.fmt -S <servername> -d <dbname> -U <username> -P <password>

Created the flat file using this format file as below.

bcp "my_sample_sp" queryout C:\table_output.txt -f C:\format_file.fmt -S <servername> -d <dbname> -U <username> -P <password>

File is created with all unreadable characters. I understand that native option is to only create files readable by SQL servers. I tried almost all options around this which are mentioned in the referred site.

Using SQLCMD tool, I was able to create the required output. As the count of records I need to export would easily cross 5 million records, I was told that SQLCMD tool is not suggested for that much count.

I tried all possible approaches before posting here. Any help which can guide me in the correct direction to create a fixed width flat file from a SP output is much appreciated.


texts:
I have installed PostgreSQL on a Windows XP laptop so I can learn about using PostgreSQL. My problem is that the PostgreSQL tutorial starts of with the instruction

To create a new database, in this example named mydb, you use the following command:

$ createdb mydb

My problem is that it doesn't tell me how/where to input the command.  I have found access to an SQL Shell but it doesn't perform as expected.

What is the 'proper' way to input commands?

No GUI ?


texts: How to optimize selection of pairs from one column of the table (self-join)?

texts:
I'm using PostgreSQL 9.5.19, DBeaver 6.3.4

I have a table where one row is - user's name, place he attended, time when he was there

I need to select all pairs of places where any user was (if user was at place a and place b i need row like this: user, place a, place b, time at place a, time at place b)

The ponds table:

CREATE TABLE example.example (
    tm timestamp NOT NULL,
    place_name varchar NOT NULL,
    user_name varchar NOT NULL
);

Some sample data:

INSERT INTO example.example (tm, place_name, user_name)
values
('2020-02-25 00:00:19.000', 'place_1', 'user_1'),
('2020-03-25 00:00:19.000', 'place_2', 'user_1'),
('2020-02-25 00:00:19.000', 'place_1', 'user_2'),
('2020-03-25 00:00:19.000', 'place_1', 'user_3'),
('2020-02-25 00:00:19.000', 'place_2', 'user_3');

I'm trying this script:

select
   t.user_name
  ,t.place_name as r1_place
  ,max(t.tm) as r1_tm
  ,t2.place_name as r2_place
  ,min(t2.tm) as r2_tm
from example.example as t
join example.example as t2 on t.user_name = t2.user_name
                       and t.tm < t2.tm
                       and t.place_name <> t2.place_name
where t.tm between '2020-02-25 00:00:00' and '2020-03-25 15:00:00'
  and t2.tm between '2020-02-25 00:00:00' and '2020-03-25 15:00:00'
    group by t.user_name
       , t.place_name
       , t2.place_name

Seems like it gives me the right result, but it works really slow.
Can I optimize it somehow?


texts: What would be the least intrusive method to add an email notification to the DBA team when a new sql agent job is added to production?  Would a trigger on MSDB database on insert be the way to go?  Curious as if anyone has setup any type of notification like this before.


texts: Does the number of columns been selected affect query speed?

texts:
I know some sql but don't quite understand the things behind the scene. And this question came to me the other day while I am taking shower...
If we compare these two queries below:
a.    select * from table_abc;
b.    select id, name, timestamp from table_abc;
which query is (theoretically) faster? My guess is a because in query b it takes time to pick out the 3 columns I want. But that's counterintuitive at the same time because that means getting less information takes longer. All comments/answers are highly appreciated!


texts: Oracle RAC, unable to connect to node vip

texts:
I have a new oracle RAC installation running 11.2.0.4 on redhat 6. I can connect to sqlplus from a client using the node name but it is timing out when i use the node vip

eg. first one works, second one doesn't

sqlplus system/xxxxxxx@csya-orarac1.xxxx-dmz.local:/dev1.xxxx-dmz.local
sqlplus system/xxxxxxx@csya-orarac1-vip.xxxx-dmz.local:/dev1.xxxx-dmz.local

i can successfully ping and run an nslookup against the VIP

the local_listener looks like this

local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=csya
                                                 -orarac1-vip.xxxx-dmz.local)(P
                                                 ORT=1521))))

i can successfully ping between the 2 node vip names from the servers themselves.
the VIP is defined in the /etc/hosts file, and the node vip shows when performing an ipconfig

eth1:5    Link encap:Ethernet  HWaddr 00:50:56:85:60:79
          inet addr:172.28.1.95  Bcast:172.28.1.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

the listener on the node, knows of the vip ip address

./lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2015 11:13:45

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAY-2015 10:26:10
Uptime                    0 days 0 hr. 47 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/csya-orarac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.1.91)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.1.95)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "dev1.xxxx-dmz.local" has 1 instance(s).
  Instance "dev11", status READY, has 1 handler(s) for this service...
Service "dev1XDB.xxxx-dmz.local" has 1 instance(s).
  Instance "dev11", status READY, has 1 handler(s) for this service...
The command completed successfully

this is part of a bigger issue i have been having with connecting to the SCAN name, but using the oracle trouble shooting doc 975457.1 it looks like it may be a local listener problem with the vip

any ideas?


texts: JOIN and concatenate between tables and UPDATE

texts:
The server is SQL Server 2008 R2 and SQL Server 2017. Probably a simple answer to this one, but it has me a bit stumped.
Ok so I have two tables, one has basic vendor inventory information and the other is a vendor code lookup to match it to our product database.  The vendor inventory is updated nightly from a downloaded XML file so I don't have control over how it comes in sadly.

What I'm needing to do is JOIN the two tables and UPDATE one of the columns with the concatenation of two of the columns from two different tables.

I have the concatenation done:

SELECT concat(SFXVencode, partnumber) as Pcode
  FROM KeyInventory JOIN Keybrands
    ON keybrands.vencode = keyinventory.vencode

But what I need is the ability to save this into a column in the keyinventory table.

So the concatenated productcode (what matches to our product database) needs to be saved in a column.

This is how far I've gotten, but it's not working currently.

UPDATE KeyInventory
SET KeyInventory.Productcode = (

SELECT concat(SFXVencode, partnumber) as Pcode
  FROM KeyInventory JOIN Keybrands
    ON keybrands.vencode = keyinventory.vencode
        )

I'm needing the KeyInventory.Productcode to be updated to the Pcode

I hope this all makes sense and I would be forever grateful for a fix for this.
KeyBrands

            SFXVenCode
            VenCode

            ACC
            A51

KeyInventory

            PartNumber
            VenCode
            ProductCode

            3102
            A51
            (what I need)

So the Concatenated would be ACC3102 and I need it saved into the KeyInventory table.

Sorry, cannot use html to format the table data.. argh.


texts: Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?

texts:
I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/

In it, she briefly mentions that the Fill Factor setting does not affect Heaps.

Does this still apply today, and does she mean only pure heaps that don't even have non-clustered indexes?...or to any heap table regardless if it has non-clustered indexes?


texts: Does Disabling Page Level Locking Have an Adverse Effect on Other DBs

texts:
I got the following from a vendor about what their software (“IM”) needs:

“IM needs its own SQL Server Instance if the DB is installed on a shared SQL server. This is a mandatory prerequisite because IM handles a lot things differently than a typical MS SQL standard database. These differences will normally slow down other databases (for example IM switches the SQL server from page level locking mode to row level locking mode which is faster for IM but slower for a lot of other databases - this is because of the asynchronous architecture).”

Admittedly, I don’t know a lot about the benefits of one locking type over another but I thought page and row level locking were handled inside a database.  Why would disabling page level locking have an adverse effect on other DBs in a shared instance?  Or would it?


texts: Is there a way to input field information by cvs file?

texts: I am importing a new database. I am asked to input field information in MS Access manually. Is there a simpler way? For example, import a cvs version field information into the following


texts: Logical optimization of joins and selections

texts:
I am studying query optimization. I have read that it might be preferred to apply relational selections before a logical join operation. Is there a scenario where joining first and then applying selections would be desirable?

I searched for an example of this, and found that people seemed to be saying that a query with conjunctions might benefit from this, but it is not clear to me why this should be. This is not a question about how a particular query optimizer in a particular commercial product performs, it is a question of logic.

An example would be appreciated.

EDIT:

Inner Joining allows joining first and then selection and it would be optimized approach too. Example:

SELECT * FROM employee,**(Inner Joining)** department
WHERE employee.DepartmentID=department.DepartmentID;


texts:
I am forced to manually document a legacy enterprise database. Are there some standard good practices or tools to do this?

To give you some more context, I have recently been given restricted access to a legacy enterprise database. There is very little documentation. I don't have permissions to view system tables or generate ER diagrams. I am exploring/using the database and manually documenting it as I go.

I would like to represent the schema with code (manually) in such a way that it can be parsed, say, with Python, to generate an ER diagram?

Are there tools to do this kind of thing? Or standard good practices to document the schema, annotate definitions and common keys etc?

I'm imagining some kind of a tree or graph like data structure that can be used to encode the schema, parsed with Python etc, and used to generate ERDs, etc, outside of the db management tool; (since I don't have permissions to do this inside the db management tool (Oracle SQL Developer)).


texts: LOB in frequently queried table or separate table

texts:
I have a table named FinTrans in an Microsoft SQL Server 2012 server with approx 10K-30K records at any given time and about 2K records are inserted and deleted each day.

The table has 3 nvarchar(max) columns that hold possible error messages (3 different types of messages, hence the separate columns) for a transaction. Whenever the contents of the table are listed, these nvarchar(max) columns are also read. The columns are nvarchar(max) as their contents may go beyond 4K, however chances are slim (perhaps with one record every week).

Under these circumstances is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is?


texts: How to get max field value length for each TEXT field in all tables in database

texts:
I am trying to get information about the data in my database.  I have several columns in several tables that or type 'TEXT'.  I want to perform a query that will create a result that looks like:

|table_name|column_name|max_length|
-----------------------------------
|my_table  |name       |35        |

I have the query to get all the tables and columns:

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'text'
ORDER BY table_name

But I can't figure out how to now go through each of those tables and columns to get the max length of each column.  I do have the query to get the max length of a specific column and table using:

SELECT 'my_table', 'name', MAX(LENGTH(name)) FROM my_table

How can I make a query that will get the max length dynamically based on the results of the query to get all tables and columns?


texts:
I'm working from a Mac OSx Lion that is running XAMPP on localhost. I've been trying to find a way to run MySQL through the terminal. Normally I would do mysql -u [username] -p but the command is not recognized.

Am I doomed to use PHPMyAdmin for local development?

I couldn't find anything related to this on the web, hopefully someone can give me a hand.

Gracias!


texts:
We have a SQL Server 2016 SP1 CU7 Enterprise server where we enabled the query store on a database. This is a highly used database.The Max Size has been set to 200MB and Stale Query Threshold is 1 day.
I've used sys.database_query_store_options to check the properties of the query store and everything works perfect as long as the current_storage_size_mb stays under the 200MB.
I've set Size Based Cleanup Mode to Auto but no cleanup happens, normally a cleanup should be triggered when the query store is 90% full. What happens  next is that the query store goes into readonly mode because there is no available space left.
I've tried changing Query Store Capture Mode to Auto but nothings changes.If I then change Stale Query Threshold to 30 days the actual_state_desc changes to READ_WRITE and the query store starts capturing again. The current_storage_size_mb just becomes higher than the max_storage_size_mb and after a while the actual_state_desc goes back to READ_ONLY.

Can anybody explain this behavior?


texts: mysql update unique values from 2 different tables based on 2 index fields

texts:
i have 2 tables with the same unique id. i want to change table1 unique_id sequence from (1-100) to random 8 digit ids (43342359 - 83083224) and cascade that to table2.

what i have:

- table1
    id1 (primary)
    unique_id(index)
    option_id(index)
    value1
    value2

- table2
    id2 (primary)
    unique_id (index)
    other1
    other2

what i would like to achieve:

- table1
    unique_id (primary)
    option_id (index)
    value1
    value2

- table2
    id2 (primary)
    unique_id (index)(constraint cascade)- on table1 update/delete
    other1
    other2

furthermore:

unique_ids are a range from 1 - 100 which interferes with another db with same unique_ids but different option_id. basicaly i would like to join 2 databases with the same scheme and create new unique_id primary on that, with cascade constraint as mentioned.

Update:

found a possible solution here https://gist.github.com/leenasn/875796

is there a simpler way?


texts: How to become an administrator for SQL Server

texts:
Here is the situation:

I Installed VS2013
That comes with SQL Server Express
I am not able to create a DB from VS2013
thinking that it's because of user privilege issues, I Installed SQL Server Management Studio
I have looked to figure out how to give myself Administrator privileges on SQL Server, and nothing has worked so far
I am the administrator of my Windows7
I run the SQL Server Management Studio as Administrator

Still I get error messages about not having permission to perform this action: Error 15247

Any ideas what I need to do to have permission

Link to image.


texts: Unable to access an SQL Server 2016 named instance locally via sqlcmd when only using @@SERVERNAME value

texts:
I have a server with two instances of SQL Server 2016 SP1 CU7 running, one is the default instance (on a custom port, 9433) and the second is a named instance (on another custom port, 9434).

I've already had minor issues on this setup with the SQL Server Agents (where I had to manually set each SQLServerAgent ServerHost registry key to "hostname,9433" and "hostname,9434" respectively, which I've never had to do before). Now, the outstanding issue is that we use Ola Hallengren's maintenance scripts which by default make use of sqlcmd and the (SRVR) SQL Server Agent token and I saw the scripts were failing on the named instance. Testing it manually, (SRVR) maps to @@SERVERNAME (hostname\instance) so running something like this directly on the server hosting the SQL Server instances showed the issue:

sqlcmd -E -S hostname\instance -d database -Q "SELECT @@SERVERNAME" -b
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.

Same error is seen when trying to set up an ODBC connection manually, again locally on that server. It does work if I use the port, or if I use either the FQDN or IP address without the port, for example these are all fine when executed locally on the database server and return the expected result:

sqlcmd -E -S hostname\instance,9434 -d database -Q "SELECT @@SERVERNAME" -b
sqlcmd -E -S fqdn\instance -d database -Q "SELECT @@SERVERNAME" -b
sqlcmd -E -S ip\instance -d database -Q "SELECT @@SERVERNAME" -b

So I can work round this (a further option is to create a local alias, which also works) I just want to know why it's not working as expected, on its own simply trying to connect to it locally using hostname\instance.

It works in any combination with or without the port when running the command from a remote machine (for example another SQL Server machine running the same level of software, and using the same version of ODBC to make its connection - Microsoft ODBC Driver for SQL Server Version 13.00.4466 - will happily connect to this remote hostname\instance without need for a port number, presumably it's making use of the SQL Browser successfully), it's only a problem locally. I can also log into the named instance locally from SSMS just using server\instance without the need for the port, though. SQL Browser is running, UDP port 1434 is listening and open in the firewall. It's not a clustered instance. Running an nslookup on the hostname resolves to the expected FQDN and IP address.

I just can't work out why this one combination isn't working and only when running locally on the server itself, the worry being that without understanding this there may be other related issues currently unseen. Hopefully it's something simple and overlooked (or changed but valid behaviour), but this sort of thing has worked as expected on SQL2012 and others and I can't see what's been done differently with respect to the setup here.


texts: What's the advantage of the inet datatype in Cassandra vs a plain text string?

texts:
What is the inet datatype really for? The documentation refers to its usage having something to do with Python but I'm not much of a Python guy so that aspect is beyond me. Is there any advantage to using inet over just a plain text representation of an IP and what is it?

If its pertinent, I'm using PHP 5.6 and Apache Cassandra.


texts:
For almost all my databases, when I run a SELECT statement, the length of the column displayed is sized to the width of the data being queried.  However, I have two databases (Dev and Prod for the same application) that seem to default all column sizes to either 32 characters wide.  If the data is wider than 32 characters or a column alias is used, it defaults the column to 128 characters wide.

Here are some examples from one of many good systems:

>select '1234567890' XX_Identifier from dual;

XX_IDENTIF
----------
1234567890

>select '12345678901234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------
12345678901234567890

Here are some examples from one of the bad systems:

>select '1234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------------------
1234567890

>select '12345678901234567890' XX_Identifier from dual;

XX_IDENTIFIER
--------------------------------
12345678901234567890

I've checked the sqlplus settings and they are all the same.  I didn't see any database parameters that looked out of the ordinary and they both have identical NLS parameters.  Both databases use the same character set.

I realize I could define a format for every column being queried, but this should not be necessary as it is not on any other DB.

The database having this problem is 11.2.0.1, while all our others are 11.2.0.4, so perhaps there is a bug that I haven't been able to find.  The sqlplus client I am using is 12.1.0.1 in both cases.

Update:
The symptoms don't match exactly, but Doc ID 330717.1 talks about column widths in sqlplus and basically says you can't guarantee anything without setting the format.  I can accept this, but since the behavior is consistent, it seems like a cause can be determined.


texts: Mysql match on two columns and get count

texts:
How do I get a count of?

+---------+-------------+
|   col1  |   col2      |
+---------+-------------+
|     Joe | April       |
|    Fred | May         |
|     Sam | April       |
|     Joe | April       |
|    Mark | Jan         |
|     Joe | Dec         |
+---------+-------------+

In MYSQL how do I get a count on Names that match only if Month also match?
IE
Joe    April should be only one...


texts:
I run a php site with mysqli. I got 3551496 Connections, but there were only 30 processes

How can It be
What should I do about it?
The server is up for 6 month, The problem is that I keep getting too many connections error


texts: When you back up a database and scripts are running at the same time, at what point in time does the backup reflect?

texts:
If I run a SQL Server database backup via Management Studio and there are scripts running at the same time, does the backup reflect the point in time when the backup was started or when it ended? My db is about 10GB so the backup takes some time, meaning there's lots of things that could go on in the meantime.

I'm using SQL Server 2008 R2

I just posted this question in SO but think this is probably a better place for it?


texts: Dropping multiple foreign keys of unknown name

texts:
I have a DB schema generated by Hibernate. Hibernate generates FKs by assigning them a generated name like FK0123456789ABCDEF.

As part of the upgrade process of an application installed at a customer's site, I need to drop some FKs created by Hibernate long ago. I cannot be sure that their names remain the same from my local installation and the customer's site. Worse, their DBA won't allow me to trickle with their UAT and PROD Dbs with a Management Studio client: they want one script to run both on UAT and PROD without any error (violators will be rolled back).

I know how to indentify these FKs. FKs from tables labeled TA_xxx to tables labeled XTB_xx.

So I ran the following in local

SELECT
    name AS FkName,
    OBJECT_NAME(parent_object_id) AS ReferencingTable,
    OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) LIKE 'TA_%'
AND OBJECT_NAME(referenced_object_id) LIKE 'XTB_%'

Result:

FK2A3B4D9E4EF09A0A  TA_BONDS    XTB_IMPORTS
FK2AC809114EF09A0A  TA_LOANS    XTB_IMPORTS
FKFCE5B2C695243BE8  TA_DEAL_SUBMISSIONS XTB_EXPORTS
FKFCE5B2C64757176B  TA_DEAL_SUBMISSIONS XTB_EXPORTS
FK2A52C11573F2E7FF  TA_DEALS    XTB_ORGANIZATIONS
FK2A52C11595243BE8  TA_DEALS    XTB_EXPORTS
FK2A52C1154757176B  TA_DEALS    XTB_EXPORTS

They are 7 both on my installation and on customer's site. I wanted to adapt a script like the following

DECLARE @fkConstraint SYSNAME
DECLARE @tbName SYSNAME

SELECT
    @fkConstraint= name,
    @tbName = OBJECT_NAME(parent_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) LIKE 'TA_%' AND OBJECT_NAME(referenced_object_id) LIKE 'XTB_%'

DECLARE @DropStmt NVARCHAR(500)

SET @DropStmt = 'ALTER TABLE [dbo].'+@tbName+' DROP CONSTRAINT ' + @fkConstraint

EXEC (@DropStmt)
GO

I think I could repeat the above block 7 times to get rid of all FKs, but I'd ask if there is a more automated way, like "DROP CONSTRAINT ... WHERE IN (SELECT ...)"

Any ideas?


texts: How to Reclaim Data Free in InnoDB Tables?

texts:
I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf
    innodb_file_per_table       =1

STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1

STEP 06) Restart mysql

STEP 07) Reload SQLData.sql into mysql

after that steps, i executed SELECT TABLE_NAME, TABLE_SCHEMA, Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql') AND Data_Free >0;

at sqlyog, this is the output:

after that i run:

mysqlcheck -u root -p$pw --debug-info --all-databases --auto-repair

mysqlcheck -u root -p$pw --debug-info --all-databases --optimize

then i query show table status on sqlyog, what i am expecting is under data_free column will be a value of zero, since on my current setup its should release unused space space.

and my question is, is there something wrong in what i did? how am i gonna do it right in reclaiming unused space in innodb tables?

mysql 5.5 on rhel 6.3

Regards,

John


texts: Postgres constraint function on a single column

texts:
Is it possible to create a constraint in Postgres that will run a function, but only if a specific column is changed?

Right now, I have this:

ALTER TABLE public.employee
  ADD CONSTRAINT pin_is_unique_in_company CHECK
     (fn_pin_is_unique_in_company(id, company_id, pin));

I only want this to run if the pin column has been modified.


texts: SQL Server Management Studio login prompt will never forget users other than 'sa'

texts:
When I login to a database, using some other user than 'sa', that username will stick the next time I login.
If I input the username sa and password then log in, the next time I open the login prompt for that server, it will not remember that I logged in as sa, but show the user not being sa.
Why?


texts: How can I add a user and specify a password for a in IBM Data Studio?

texts:
In Data Studio I can connect to a sever/database, go down to Users And Groups, right-click on Users and add a new one. But the program never asks me for a password. How can I specify it?
Also, no matter how many times I tried to "Save changes" they are never saved. I even tried to "generate change" and run it, but it always fails.
I am connecting as the only user that exists on the server: db2admin. If db2admin has no authority to manage users, then who has?


texts: Should Archive Tables have their own Surrogate Identity Key

texts:
Should archive tables have their own surrogate identity id/key? For a customer sales table example:

We have original table, and then an additional archive table with surrogate archive id.
Rules for primary clustered indexes are:

Ever Increasing
Narrow
Unique
Static

Reference: Effective Clustered Indexes (Red-Gate Hub)

At the last company I worked for, there was a thought, that if we accidentally missed a day or period of importing, e.g. imported data for May 3, accidentally skipped May 4 (issue with system), and imported May 5, that without the new archive id, we would have to reinsert data between the pages, causing fragmentation, and slower inserts.

With the archive id, we can just add on in an ever-increasing manner.

Just inquiring if adding archiveid column is a standard industry practice.

Original Table:

create table dbo.CustomerSalesId
(
    CustomerSalesId bigint primary key identity(1,1),
    CustomerId bigint not null,
    PurchaseDate datetime not null,
    Amount decimal (10,2) not null,
    .........
}

Archive Table:

create table dbo.ArchiveCustomerSalesId
(
    ArchiveCustomerSalesId bigint primary key identity(1,1),
    CustomerSalesId bigint,
    CustomerId bigint not null,
    PurchaseDate datetime not null,
    Amount decimal (10,2) not null,
    .........
}
create unique index ux_CustomerSalesId on ArchiveCustomerSalesId(CustomerSalesId)


texts:
How Do I give permission for user to Truncate all tables in database?

The minimum permission required is 'ALTER on table_name' per articles below. With this, how do I give permission to allow user to Alter All tables in a database, so they can truncate? Is it something like for 'sp_MSforeachtable' or other method?

Thanks,

What permissions are necessary for truncating a table?

https://www.mssqltips.com/sqlservertip/4112/minimum-permissions-for-sql-server-truncate-table/

We have SQL Server 2016 Enterprise


texts: mysql 8 - rds - 1022 Can't write; duplicate key in table

texts:
Documenting this error and solution here in case it could help anyone.
We have a MYSQL 8 database on Amazon RDS.
The below group by used to work just fine on Mysql 5.7 but it started giving
 1022 Can't write; duplicate key in table  '/rdsdbdata/tmp/#sqlf80_1656bc_0'
 error once we upgraded to Mysql 8.

GROUP BY DATE_FORMAT(CONCAT(YEAR(performance_stats_date),'-',
MONTH(performance_stats_date),'-',DAY(performance_stats_date)),
 '%Y-%m-%d')


texts:
I read the following on this page

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

  bulk_insert_buffer_size MyISAM uses a special tree-like cache to make
  bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...),
  (...), ..., and LOAD DATA INFILE. This variable limits the size of the
  cache tree in bytes per thread. Setting it to 0 disables this
  optimization. Note: This cache is used only when adding data to a
  non-empty table. The default value is 8MB. This variable was added in
  MySQL 4.0.3. This variable previously was named
  myisam_bulk_insert_tree_size.

I take this to mean that bulk_insert_buffer_size has no bearing on
InnoDB tables, only on MyISAM tables. Is this correct?


texts: SQL Query question (items not having some value in a column)

texts:
Here is a conundrum I need to solve with some T-SQL Magic, I tried and failed.

It's one of these things that, before I think of the actual solution I think I can solve in 3 minutes with some T-SQL, but then when the "rubber meets the road" I just can't picture in my mind the right operators to achieve this logic.

I'm wondering if anyone in the Stack Exchange community can find an elegant solution to this one. I guess with a combination of 3 or 4 queries using temp tables or cursors I could find the solution, but it would not be SET based...

I have a table that looks like this (more or less...)

CREATE TABLE Computers (
   ID               int IDENTITY(1,1),
   ComputerName     varchar(255),
   OS               varchar(255),
   AppName          varchar(255),
   AppVersion       varchar(10)
)

So, every row has a computer name, an OS value (that can be either Windows 7 OR Windows 10) and then a description of an application (e.g. AppName = "Notepad++", AppVersion = "7.5.6")

ComputerName   | OS           | AppName   | AppVersion
Computer 1     | Windows 7    | App 1     | 1.0
Computer 1     | Windows 7    | App 2     | 1.0
Computer 1     | Windows 7    | App 3     | 1.0
Computer 2     | Windows 10   | App 4     | 1.0
Computer 2     | Windows 10   | App 5     | 1.0
Computer 3     | Windows 10   | App 4     | 1.0
Computer 4     | Windows 7    | App 4     | 1.0
Computer 4     | Windows 7    | App 5     | 1.0
Computer 5     | Windows 7    | App 1     | 1.0
Computer 5     | Windows 7    | App 4     | 1.0
Computer 5     | Windows 7    | App 5     | 1.0

The way it works is like this: if a computer has 10 apps installed, then you will have 10 rows in this table (one for each app). The computername and OS will be repeated (please, don't start a discussion about 2nd normal form... this is strictly a question about a query to solve a problem).

So, the problem I need to solve is: I need to find a list of computers WHERE the OS is Windows 7 (that's EAAAASY) but...

Those computers should only show up in my list if ALL the applications that are reported in this table for that PC are reported as being used on other Windows 10 computers.

If a Windows 7 computer has apps that are not listed in a Windows 10 computer, then they should not show up in this list. Each and every app for a computer must comply with this rule for the computer to qualify on my list.

The idea is to know that the computers I'm getting in my SELECT have all apps that I know work for Windows 10, so they should be safer to migrate.

Sample:

For those trying this at home, here is a CREATE TABLE and some INSERTs you have the right idea of what my data looks like

-- Create the table
 CREATE TABLE Computers (
    ComputerName    varchar(255),
    OS              varchar(255),
    AppName         varchar(255),
    AppVersion      varchar(10)
 )

 -- Insert some values...
 INSERT INTO Computers
 VALUES
 ('Computer 1', 'Windows 7', 'App 1', '1.0'),
 ('Computer 1', 'Windows 7', 'App 2', '1.0'),
 ('Computer 1', 'Windows 7', 'App 3', '1.0'),
 ('Computer 2', 'Windows 10', 'App 4', '1.0'),
 ('Computer 2', 'Windows 10', 'App 5', '1.0'),
 ('Computer 3', 'Windows 10', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 5', '1.0'),
 ('Computer 5', 'Windows 7', 'App 1', '1.0'),
 ('Computer 5', 'Windows 7', 'App 4', '1.0'),
 ('Computer 5', 'Windows 7', 'App 5', '1.0')

With this example, the only computer that qualifies for my query would be Computer 4, as is the only Windows 7 computer where ALL apps associated with it (Apps 4 and 5) also exist in at least one row for a Windows 10 PC.
Computer 5 does not qualify because one of the apps associated with it ("App 1") only exists in this set on another Windows 7 computer (Computer 1) so I can't say that this app is already working on a Windows 10 PC.
Computers 2, and 3 are Windows 10. So that should be enough to discard them....
Computer 1 should also NOT qualify, all their apps are not running in Windows 10 PCs.

Hopefully makes sense...


texts: When and why should I reset session in a connection pool?

texts:
I am using Python to implement a high speed query push service into a MySQL database.
Considering a broad usage of various MySQL elements like temporary tables in stored procedures, when and why  should I use pool_reset_session feature on a 256 connections-based pool?


texts: Constraints on a junction table of many entity types?

texts:
Let's say I have the following database table and columns:

Event: event_id, event_name
School: school_id, school_name
Automobile: automobile_id, automobile_name
User: user_id, username

AssetPermission: user_id, asset_type, asset_id

In the AssetPermission table, the asset_type can take on values like event, school, and automobile, and the asset_id takes the primary key value from rows in event, school or automobile depending on what the asset_type is.

My question is can you apply database constraints (or some other mechanism) to make sure there are no orphan records in the AssetPermission table that might result from deleting records in Event, School and/or Automobile?

I work with either mysql or sql server, so not sure if the technology choice influences the answer.


texts: Can I configure Oracle such that a Windows client can connect to a Linux Oracle server without a password?

texts:
I am attempting to configure Kerberos authentication to an Oracle 11.2 database from a Windows client.  The goal is to have an IIS Application Pool connect to the DB via its service account credentials without having to store a password in the connection string.

Environment information:

Windows 2008R2 Active Directory
Oracle 11.2 DB on Linux AIX host
Windows 2008R2x64 Client PCs

Using Oracle ODAC .NET driver to connect to DB via IIS Applicaiton Pool

Is this feasible?  I've seen several posts that indicate it is, but I am unable to get even sqldeveloper to authenticate with Kerberos from both the DB server itself and the Windows client.

I am able to retrieve a kerberos ticket on the DB server (okinit works).

When I attempt to connect in sqldeveloper on the DB server, I get a password prompt in the UI, which closes as if the password was accepted, but nothing opens afterwards.  Attempting to expand the DB node results in another password prompt that behaves similarly (no error, just goes away with no additional UI).

I fear if I cannot even get sqldeveloper to work, there is little hope for ODAC.


texts: Sql Server, a single threaded query spawns multiple threads (or internal parellelizaion during execution)

texts:
In Sql Server, when I run a procedure or a query that takes multiple minutes, sometimes spawns multiple threads. Even though there is no MAXDOP hint or necessity in the query, I see multiple instances of the same query (session ID = 52).

May be it occurs in other scenarios, but I've mostly noticed when the status goes to something like 'suspended' or 'RUNNABLE'. The session_id remains the same though.

See the images below for both scenarios. Is there a reason behind it?

Same query when "running" state.

But when status changes to other, the number of instances of the same session=52 increases. There are more than 25 instances.

Just curious.


texts:
When I perform this query:

SELECT `tablename`.* FROM `something`.`tablename`

I get a table with column names that contain dots:

tablename.c1 | tablename.c2 | tablename.c3
------------------------------------------
     a       |       1      |     2
     b       |       1      |     3

I don't want this, I just want the column names c1, c2 and c3, I can solve this by writing the following query:

SELECT `tablename`.`c1` as `c1`,
       `tablename`.`c2` as `c2`,
       `tablename`.`c3` as `c3` FROM `something`.`tablename`

I however have many columns which makes it a very long query. How can I rename the columns from the first query or how can I get this right from the start?

(p.s. the query I'm using contains multiple table references that is why I specify the table name tablename.*)


texts: Why are NULL values not being recognized as null in my CASE expression?

texts:
I've tried writing this two ways. The first one ignores the WHEN NULL THEN '' part, and the second one just doesn't work.
SELECT

    buffer_mol,

    (CASE buffer_mol
        WHEN NULL THEN ' '
        ELSE concat(buffer_mol, ' mM, ')
    END) as "case column"

FROM purified_enz pur FULL OUTER JOIN produced pr
ON pr.pk_produced_id = pur.fk_produced_id;

I get:

First I tried
SELECT

    buffer_mol,

    (CASE buffer_mol
        WHEN NULL THEN ' '
        WHEN IS NOT NULL THEN (buffer_mol, ' mM, ') -- <- line 20
    END) as "case column"

FROM purified_enz pur FULL OUTER JOIN produced pr
ON pr.pk_produced_id = pur.fk_produced_id;

But I get this error with and without IS being in front of NOT NULL.
ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:
*Action:
Error at Line: 20 Column: 14


texts:
I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration.  Meaning, that even before I start tuning PostgreSQL configuration, I would like to understand if what I'm seeing in the benchmark results correlates to the physical reality of the hardware.

I would really, really love if there was a table somewhere showing transactions per second as function of disk speed and/or available memory and/or CPU speed / number of cores. Or, a formula to calculate the best performance for the test, with hardware values plugged into it.

If there isn't a formula, but you still feel confident that you can tell what's a "good performance" given these specs, I'd still want to hear from you:

2 vCPUs Intel(R) Xeon(R) CPU E5-2676 v3 @ 2.40GHz
4 GB of RAM
SAN should be comparable to 5000 IOPS 100 GB EBS (NVMe)

What I'm getting right now is around 500 TPS on the TPC-B-like built-in test. Does this number make any sense? What would you hope to achieve given this sort of system? (Again, this system is not supposed to be a production database server, it is used to benchmark the SAN). If it doesn't make sense to benchmark on a less capable system (you cannot extrapolate from the results the behavior on a real system), then what kind of hardware (virtual of course) would you suggest for the more realistic test?


texts: I am in the process of replicating my database so i can have a  master slave configuration, one of the issues i have is with security i am basically generating my server/client keys and certificates using openssl i also generate my own CA key and certificate to self sign, i understand the issues with self signing certificates on a public website, but do you think this will be as a serious problem when used in replication?


texts: How do I create a user-input column in SSRS?

texts:
I am new to SQL Server Reporting Services and report designing; can anyone help me in this?

I have two questions:

How can I create a column for users to input data?  After that, I need to perform some operations on the data entered by the user, which I need to display in another column.
How can I round a decimal to the nearest integer?  For example, 6.03 to 6 and 6.52 to 7.


texts: SQL Server: calculating memory requirements for Full db backups

texts:
In 2018, we inherited a production SQL Server 2012 FCI running on Windows 2012 with 32 GB RAM. SQL Server max server memory was set at 23.6 GB, and things were running fine.

However, in 2019, we migrated these databases to a SQL Server 2016 FCI. After this migration, our Full backups began intermittently failing due SQL Server restarts. The log seemed to indicate these restarts were due to low memory.

I noticed all of these SQL Server restarts only happened when a full backup was running for our biggest (~80 GB) db. (Incidentally, in case this matters, this db is set to simple recovery model. I have 4 other dbs in full recovery model on this instance: 10 GB, 110 MB, 100 MB, and 50 MB.)

Each time these "low memory restarts" occur, I have been incrementally increasing RAM and max memory. Currently, I'm at 56 GB RAM and max memory is at 45 GB.

From your experience, does it seem unusual for an 80 GB database to require 45 GB max memory during full backups? Can you please share any ideas how I can better identify how much memory my full backup truly needs? Unfortunately, I don't have a non-production system with similar specs as this one.


texts: Postgresql: Enable TLS for localhost connections

texts: What are the advantages and disadvantages to enable TLS to a Postgresql that only gets connections on localhost?


texts: Accept null value on parent column in multipart relationship

texts:
EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer.

Each Category, except any 'built-in' Category, is only visible to
its respective Owner.
Each item ("OwnedItem") must be assigned a Category that is visible
to the item's Owner.

The challenge: since Owners can only see their own Categories and 'built-in' Categories, the data is invalid (for example) if one Owner's items are assigned a Category from another Owner, unless that Category is owned by no one.

Because of this, I would like to accept a null value (or a fixed value, e.g. -1) on one column of the parent in a multi-column relationship.

-- Categories have owners, or may be 'globally shared'
CREATE TABLE Category (
    Id int IDENTITY(1,1) NOT NULL,
    OwnerId int NULL,
    Title nvarchar(50) NOT NULL,
    CONSTRAINT PK_Category PRIMARY KEY (Id ASC)
)

SET IDENTITY_INSERT Category ON
GO
INSERT INTO Category
    (Id, OwnerId, Title)
VALUES
    (-1, NULL, 'Hidden'),
    (0, NULL, 'Default')
GO
SET IDENTITY_INSERT Category OFF
GO

-- Items have owners,
-- and must be assigned a category from the same owner,
-- or be assigned a global category
-- i.e. prevent assignment of items to another Owner's private Category
CREATE TABLE OwnedItem (
    Id int IDENTITY(1,1) NOT NULL,
    CategoryId int NOT NULL,
    OwnerId int NOT NULL,
    Description varchar(50) NOT NULL
    CONSTRAINT PK_Ownedtem PRIMARY KEY (Id ASC)
)

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_OwnedItemCategoryId
    FOREIGN KEY (CategoryId)
    REFERENCES Category (Id);

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_CategoryOwnerOwnedItemOwner
    FOREIGN KEY (OwnerId)
    REFERENCES Category (OwnerId)

    -- How can I say:
    -- OR Category.OwnerId IS NULL

    ;

How can I allow OR Category.OwnerId IS NULL immediately above?

INSERT INTO OwnedItem
    (CategoryId, OwnerId, Description)
VALUES
    (-1, 23, 'Person23s hidden item')
-- SHOULD SUCCEED


texts:
I have a Postgres database I am hosting in a quad core server with 8 GB of RAM.

At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When I do "top", most of my cores have very high IO wait % - 50% - 70%.

I have a feeling I need to fine-tune some parameters in my Postgres. But which ones would effect IO usage the most? Would adding more cores help in reducing IO usage?


texts: Cube Designer Aggregation Wizard Optimization Zero Percent Every Time

texts:
I am pretty new to SSAS.

I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an issue that indicated a field I had removed was being used in aggregations. Thus, my project would not longer build. I searched (even in View Code) for this field and could not find it. Then, perhaps a rookie mistake, I removed the aggregations, thinking I would create new ones once I was able to build the project. However, all attempts to create aggregations fail. The Agg design wizard either creates 0 aggregations for 0% optimization, or it creates some (if i make specific selections in the wizard instead of keeping default choices), but still with 0% optimization. I get the same behavior when running the wizard from Management Studio.

All of the same settings on attributes for OptimizedState, etc, are in place from then the original aggregations were created. I am just now getting back to this after many more changes so simply rolling back is not an easy option.

I even tried creating some manually that I was confident would help. But using Profiler has shown that those aggregations are ignored.

How can I determine why the Agg Wizard is behaving this way? And how can i get some aggregations created?

Thanks in advance.


texts: Query tables with information_schema in pgsql

texts:
I am trying to query the tables contained in a schema from an insert trigger on another table.

My process is :

Manually create the schema with DB Manager inside QGIS.
Load tables foo, bar, baz.
Insert a reference to that schema inside a table which trigger a function to perform queries on these tables.

I am fine to create a trigger/trigger function, but unable to create the query to grab the information inside the tables

EDIT :

WITH info_schema_subset_table as (SELECT table_schema,table_name,
array_to_string((regexp_split_to_array(table_name,'_'))[4:array_length(regexp_split_to_array(table_name,'_'),1)-1],'_')) END as new_table
FROM information_schema.tables
where table_schema = NEW,schema_name
ORDER BY new_table ASC)
EXECUTE 'CREATE TABLE $2 as (SELECT * FROM $1);'
USING info_schema_subset_table.table_schema || '.' ||info_schema_subset_table.table_name,info_schema_subset_table.new_table;

I am unable to run a simple execute. What am I missing?


texts: how to "revoke" a "GRANT OPTION" from an mysql user/database?

texts:
i have a mysql installation with lots of users and grants. But one grant is unique, since its supposed to be a simple DB user and usually does not need GRANT privileges. However someone added it when the account where added. It looks like this:

GRANT USAGE ON *.* TO 'freg2'@'%' IDENTIFIED BY PASSWORD '*XXXXX';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, \
    REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, \
    LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, \
    CREATE ROUTINE, ALTER ROUTINE \
    ON `freg2`.* TO 'freg2'@'%' WITH GRANT OPTION;

I searched the net (and this site) for a solution on how to remove that "GRANT OPTION" thingy, without recreating the whole grant/account . so far without luck.

Is there really no way just to remove "this" privilege or right from that account ?

thanks in advance.


texts: SQL Server SSL Encryption - SelfSign Cert working - why?

texts:
I realize this is strange behaviour and I wonder if it is supposed to be working that way when using SQL Server generated self-signed certificate.

Client (Management Studio) with "encrypt connection" option set, client will verify self-signed cert on SQL Server side and fail because the self-sign cert is not trusted

Client (Management Studio) without 'encrypt connection' option set but "Force Encryption" is turned on at SQL Server side using SQL Server Configuration Manager. Client will not verify the self-signed cert and will connect successfully with the connection encrypted.

Questions

Why doesn't the client attempt to verify the SQL Server self-signed cert when encryption is enforced on Server Side? Why does the client only verify the self-signed cert when encryption is enforced on the client side?

If cert authentication is not done on client side for server-side encryption, what's the point of using a CA signed cert?

To be more precise: Why doesn't the client (Mgmt Studio) attempt to verify/authenticate the cert (be it self-signed/CA signed) when only server-side encryption is turn on ? Why does the client only authenticate/verify the cert given by the server when client-side encryption ( "encrypt connection" ) is turned on?
I added a signed cert (signed by an internal CA) to be used for server-side encryption. This requires the internal CA cert to be installed (into trusted root CA) on the client machine, but I did not do that. However, the client (mgmt studio) still connects successfully when server-side encryption is turned on.


texts: How to track blocking that happen for less than a second - SQL Server

texts:
I am trying to troubleshoot a blocking issue which happens for less than a second.
The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as per the agreed SLA.
We had some lock escalation issues with the new code release which we were able to solve with reducing the batch size in the updates.
Even with the small batch size, we suspect that the new sp is blocking the same rows that the OLTP transactions is updating.

I need to find the session that is getting blocked and the resource its waiting on. As per my understanding "blocked process threshold" can set for a minimum of 1 second and so this will not capture the blocking.

I am experimenting with wait_info and wait_completed x events.

Is there any other way we could track this. Thanks


texts: Access denied when trying to delete files with xp_cmdshell

texts:
On a SQL Server 2012 SP2 instance the query

xp_cmdshell
'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'

fails with the output:

  Access is denied.

Both of these return the windows account running the SQL Engine and SQL Agent:

EXEC master..xp_cmdshell 'set username'

  USERNAME=AngeliaSQL

EXEC xp_cmdshell 'whoami'

  ntdomain\angeliasql

The windows account has Full Control on the \morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ and all files in the folder.

Is there another way to check permissions?


texts:
We are having a shared hosting plan and they are saying  that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with required data(data from from our local MYSQL Timezone related tables. How to view the code syntax for MySQL "CONVERT_TZ" function?

Thanks
Arun


texts:
I've got a quick question , that would be great if anyone can help me with this problem:
I have 2 SQL 2012 databases A & B , they are both Identical same tables and same structures as well.
Data is inserted into DB A and on regular schedule I need the data to be inserted into DB B automatically and APPENDS to the existing data in DB B,after that, data in DB A gonna deleted and the whole process starts over again with new records.I couldn't preform this action using any methods of replication because once the data is deleted from DB A same thing gonna happen in DB B.
On the other hand these data contains FK pointing to other tables in the database so if the first problem solves and it starts APPENDING data to the existing data on DB B all these FK should be updated with new values in DB B.
Do you guys have any solution using SQL itself or any other tools?


texts: How many passes of a table are done with UNION ALL?

texts:
I am trying to run a single query that will make a single pass through a table, and was wondering if by using UNION ALL to make counts of total row unioned with counts of unique rows so I would be unioning a query with from the same table.

Would this operation require a double passing of the table?


texts: User cannot select from table but their permission appear to show that they should be able to

texts:
I attempted to revoke delete permissions to most users in a database by using the deny and revoke sql commands and checking the deny box for delete when adding the table as a securable in the UI.

When I did that, the users are now not able to select from any table in the database. They have select permission granted in the UI for all tables including the table I am trying to revoke delete permissions to.

I tried to recreate what I did on a backup copy of the database and I have not been able to deny select on the tables.

The only deny permissions I found on any of the users were deny delete on all objects and deny on the one table I am trying to prevent delete on.

I did not find any deny selects on any users or roles.

No users are part of the db_denydatareader role.

What is the best way to find out why the users are not able to select from any table in the original database?

I am using SQL Server 2008.


texts: Monitoring free space in SQL Server data files

texts:
I have manually resized mdf/ndf files to a big size to avoid autogrow operations on SQL Server databases.
Since the files are bigger there is very little free space on disk partitions and the sysadmins keep alerting me that I'm running out of space.

Because I resized them, there is a lot of free space in the data files but one can't notice it looking at file sizes/disk free space.

How can I monitor the real % usage of data files? I would prefer using perfmon counters. I am conerned that when the file really runs out of space SQL Server won't be able to allocate enough space and will crash.


texts: MySQL Service won't start with error [ERROR] InnoDB: Trying to access page number 4294963199 in space 0

texts:
My MySQL installation on Ubuntu 18.04 crashed and now won't start.

The error log says

  Trying to access page number 4294963199 in space 0, space name
  innodb_system, which is outside the tablespace bounds. Byte offset 0,
  len 16384, i/o type read. If you get this error at mysqld startup,
  please check that your my.cnf matches the ibdata files that you have
  in the MySQL server.

Some Googling led me to believe this could be a corruption issue.

I edited the mysql config adding innodb_force_recovery = 5 to the mysqld section which allows the server to start.

I was then able to run a mysqlcheck to check for corruption:

mysqlcheck --all-databases

Which showed all tables as ok (Although one reported note: The storage engine for the table doesn't support check)

Where do I go from here? The error log is suggesting corruption but mysqlcheck does not find any


texts: How to print out the query SQL text of a transaction by querying the "fn_dblog" or DBCC LOG('DataBaseName')?

texts:
At some point in the last 24 hours some changes were made to a customer SQL server 2008 (v 9.0 SP4) by one of a couple if possible applications that use the DB.

Its not particular critical, but it would represent a useful insight into how these applications are using the tables if I could dump out SQL statements corresponding to the time periods.

The tables I am interested in, do not change very often and hence transactions logs applying to those particular tables should not be very large in total rows.

I see the "fn_dblog" function and DBCC LOG('DataBaseName')  commands, and seem to have quite a bit of related information, but I am not sure how to take the next step and get them to produce a report of the actual changes.

There are some details in this blog post;
http://janiceclee.com/tag/fn_dump_dblog/

USE AdventureWorks
    GO
    SELECT [Transaction ID], count(*)
    FROM fn_dblog(DEFAULT, DEFAULT)
    where AllocUnitName LIKE '%Production.TransactionHistory%'--table name
    GROUP BY [Transaction ID]
    HAVING COUNT(*) >= 113443 --(estimated/actual) number of deleted records

However I don't have any details like that, just the table name (and the primary keys if necessary I can get that)

and another blog post that is using a similar example data given their table naming;
http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-6-Using-fn_dblog-to-tell-if-a-transaction-is-contained-in-a-backup.aspx

    USE AdventureWorks;

    GO

    SELECT [Current LSN], Operation, [Transaction ID], AllocUnitName FROM fn_dblog (NULL, NULL);

    GO

The log record at that LSN is:

    00000058:00001870:0001  LOP_MODIFY_ROW     0000:00001338  HumanResources.Employee.PK_Employee_EmployeeID

This is obviously in the middle of my contrived transaction - showing that it isn't all in the full backup. The end of the transaction isn't until way later in the log:

    0000005e:00000628:01b1  LOP_MODIFY_ROW     0000:00001338  HumanResources.Employee.PK_Employee_EmployeeID
    0000005e:00000628:01b2  LOP_COMMIT_XACT    0000:00001338  NULL


texts:
We have a sharded MongoDB cluster running on 3 m4.large AWS EC2 instances (8GB RAM each)
Our Data size is 1.2GB

We set the WiredTiger CacheSizeGB to 2GB

I can see on the mongodb monitoring

Memory usage : 1.8GB
Cache usage : 1.6GB

Does that mean that my wiredTiger is using almost 80% of its cache and I should add more?

What will happen if the cache usage become 2GB ? will mongod crash or it will try to free some cache ?

in htop I can see that mongod is using only 900MB

here is db.stats

        "objects" : 2790835,
        "avgObjSize" : 437.06898473037637,
        "dataSize" : 1221403413,
        "storageSize" : 542928896,
        "numExtents" : 0,
        "indexes" : 1410,
        "indexSize" : 2697195520,
        "fileSize" : 0,
        "extentFreeList" : {
                "num" : 0,
                "totalSize" : 0
        },


texts: Why is my MySQL dump file and my phpMyadmin export file different sizes?

texts:
I am looking to create a cron job to backup my MySQL database. I am using this command:

$ mysqldump --user="User" --password="Password" -A
    --single-transaction > /home/user/Export-`date +"%F"`.sql

This results in a 2.5 MB file size. However, when I use the phpMyAdmin export feature using the same user, I get a 3.7 MB file.

Can someone please explain the differences, and if I'm doing anything wrong?


texts: Is a process created in Oracle for every SQL statement?

texts:
After reading the Oracle docs, it´s still not clear for me if an Oracle process is created for every SQL statement or just for every connection (or not really related to both).

Any comment here would be really great.

Thanks.


texts: Insert openquery results into an existing table

texts:
I have a stored procedure that queries a linked DB2 server in this fashion:

SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')'
exec sp_executesql @sql

I am trying to store that result into a new table via another stored procedure

    INSERT INTO [schema].[Table]
               ([column1]
               ,[column2])
    EXEC('schema.StoredProc')

Where StoredProc contains the aforementioned openquery

However, when I execute the second Stored Procedure (INSERT INTO), the output window prints out the text of @sql from my first stored procedure, and gives me the message:

SELECT * FROM openquery(DB2,'SELECT column1, column2 FROM table')
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Any help is appreciated


texts: How to setup db credentials for `sqlcmd` using a config file?

texts:
When I'm using mysql client, I can configure my db credentials in ~/.my.ini.

How can I achieve similar thing with sqlcmd on Linux? Where and what filename I should create?

My goal is to avoid keep repeating hostname, user and passwords for every command.


texts: VARCHAR automatically changes to VARBINARY


texts: Installation of SQL Server RAID 10 vs RAID 5

texts:
I am new to setting up SQL Servers and Vms. My predecessor used Raid 5 for C Drive where the SQL installation resided and RAID 10 for both database files and database logs.

I was wondering whether that is correct or should RAID 5 be utilized for SQL database files instead?

Any help?


texts: Optional for one entity, optional for another entity: many end optional

texts:
Please could you help me in sqlite :

agents may make bookings for performers, and performers may also make bookings for themselves. It is also possible for agents to make bookings for other functions that do not involve performers. An agent may be responsible for making bookings for a number of performers. If a performer is represented by an agent, each performer may have only one agent. The relationship is optional for both entities.

This relationship can be converted into three relations. There will be one relationship to represent the performers, another for the agents, and a third will store details of the relationship between performers and agents (where such a relationship exists).

Relation: Performers

Relation: Agents

Relation: Agent-Performer

My Question : is it necessary to create third relation ( Agent-Performer) for representing the relationship between the two relations, Performers and Agents?

what is the problem if i put the foreign key in the many end relation(Performer) and put null in some values in foreign key , without creating third relation ( Agent-Performer)?

PLease, help me in that.


texts:
I know this might be a duplicate of some other posts. But those solutions didn't work for me and I've lot of issues within those answers. Neither could I comment nor post in there, I'm posting a question.

In my case, I logged into mysql as 'root' and tried to import a sql file into the database. I then exited, did nothing and when again tried to login with the same user. But this time it was showing as

bash-2.05$ mysql -u root -p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

And I tried all different solutions(suggestions from other posts) like mysql -u root -ppwd -h127.0.0.1 mysql -u root -ppwd --protocol=TCP. These suggestions didn't work for me. I couldn't login as the root and without that I will not be having permissions to change any privileges. I'm sure user and password are the correct ones.

Apart from all this, I'm using this database for a drupal website. The website gives me an error as

PDOException: SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: YES) in lock_may_be_available() (line 167 of /home/apache2/htdocs/website/includes/lock.inc).

I couldn't trace what's going wrong with this.


texts:
I have searched but could not find anything similar. If I just run the query, I can get the result pretty quickly (~5 mins); but after I saved the query to export as CSV, the exporting part is extremely slow (>30mins).

The result only has 3 columns and approximately 30 records; I can easily copy and paste the query result I get. However, I would like to understand the strange behaviour. It's a huge database of about 7GB, that's why it's split into 4 different files. I am not allowed to use other software such as POSTgreSQL or mySQL at work. Here is my code anyway (modified the variable names):

SELECT var1, var2, count(*)
FROM (
SELECT var1,var2
    FROM [XXX10_2006-2010] INNER JOIN [XXXLong_2006-2010]
        ON [XXX10_2006-2010].ID = [XXXLong_2006-2010].id
    WHERE [XXXLong_2006-2010].var4 = 'hamburger'
        AND [XXXLong_2006-2010].cat1 = 'c'
        AND [XXXLong_2006-2010].cat2 BETWEEN 0 AND 96
        AND [XXX10_2006-2010].var5 = 'eggs'
UNION all
SELECT var1,var2
    FROM [XXX10data_2001-2005] INNER JOIN [XXXLong_2001-2005]
        ON [XXX10data_2001-2005].ID = [XXXLong_2001-2005].id
    WHERE [XXXLong_2001-2005].var4 = 'hamburger'
        AND [XXXLong_2001-2005].cat1 = 'c'
        AND [XXXLong_2001-2005].cat2 BETWEEN 0 AND 96
        AND [XXX10data_2001-2005].var5 = 'eggs'
UNION all
SELECT var1,var2
    FROM [XXX9_data_1997-2000] INNER JOIN [XXXLong_1997-2000]
        ON [XXX9_data_1997-2000].ID = [XXXLong_1997-2000].id
    WHERE [XXXLong_1997-2000].var4 = 'hamburger'
        AND ([XXXLong_1997-2000].cat1 ='1' AND [XXXLong_1997-2000].cat2 BETWEEN 40 AND 99)
        OR ([XXXLong_1997-2000].cat1 ='2' AND [XXXLong_1997-2000].cat2 BETWEEN 0 AND 39)
        AND [XXX9_data_1997-2000].var5 = 'eggs'
UNION all
SELECT var1,var2
    FROM [XXX9_data_1993-1996] INNER JOIN [XXXLong_1993-1996]
        ON [XXX9_data_1993-1996].ID = [XXXLong_1993-1996].id
    WHERE [XXXLong_1993-1996].var4 = 'hamburger'
        AND ([XXXLong_1993-1996].cat1 ='1' AND [XXXLong_1993-1996].cat2 BETWEEN 40 AND 99)
        OR ([XXXLong_1993-1996].cat1 ='2' AND [XXXLong_1993-1996].cat2 BETWEEN 0 AND 39)
        AND [XXX9_data_1993-1996].var5 = 'eggs'
)
GROUP BY var1, var2

EDIT: In case this little bit of detail helps: It takes slightly more than 10 mins for the export text wizard to show up, then the rest of the process takes another 20 mins or so. (sometimes much longer though)


texts: How do I exclude unchanged fields in Flashback query resultset?

texts:
Is it possible to exclude unchanged fields in Flashback query resultset?
Consider I have following table
create table first_table
(
    id int generated as identity,
    name NVARCHAR2(1024),
    age smallint,
    notebook nclob,
    userpic clob,
    salary float
)

If the table has very frequent updates (e.g. on notebook field) following versioned query
select ROWID, VERSIONS_OPERATION, VERSIONS_STARTSCN, VERSIONS_STARTTIME, VERSIONS_XID, id, name, age, notebook, userpic, salary
from FIRST_TABLE versions between scn 1469193 and 1482882;

will pull heavy userpic value for every row even though it's the same.
Can I somehow avoid that and instead get NULLs for unchanged values ?


texts: How to get the registers in the highest time of period

texts:
I have 2 tables all with relations and I want to get the contracts in the highest time of period in order to do a historical report whether if the time of period is closed or active.
This is the code that I have or that I've been trying
select
  a.id
 ,a.ordinalperiod
 ,b.name
 ,a.fecin
 ,a.fecend
 ,b.dtend
 ,a.contrato
From
  historiccontrat a
Left join
  periodo b
    On a.id = b.id
      And a.ordinalperiod = b.ordinalperiod
Where
  a.fecend in
    (
      select
        max(b.dtend)
      where
        a.id = b.id
          and a.ordinalperiod = b.ordinalperiod
          and b.dtend <> '4000-01-01'
    )

The thing is that the employee have 5 period of times that works in the company like this
Id  |   período     |   dt_start    |   dt_end
000 |   1           |   2012-01-01  |   2012-04-01
000 |   2           |   2013-05-01  |   2016-04-03

And in each of these periods of time have different type of contracts.
What I want is that the just the contracts in the last period.
But the result with my query is the row with the data of the period 2 but not the contracts.
Thank for your help.


texts: SQL Server 2016 stretch database - namespace error

texts:
I'm using my MSDN and using the 'adventureworks' db (running SQL Server 2016) and running the task to enable a stretch database but running into an error.  I've logged into Azure with my MSDN account and have setup a few VM's etc. so appears my account works.   From my workstation I go to setup the stretch database with my MSDN azure credentials but getting this error:

  Provisioning of Azure Sql Server stretchserver-XXXXX--adventurewor-20160720-143153189 in resource group stretchgroup-spodev150697-sql2016-westus2 failed.
  Details : {"error":{"code":"MissingSubscriptionRegistration","message":"The subscription is not registered to use namespace 'Microsoft.Sql'."}}

Any thoughts on what steps I need to take to get this registered to work?


texts:
How can I partitioning oracle table when the table still working?

Explain:
- I need to partitioning table of Oracle but the table didn't partitioned before.
- I have many scheduler I can't close it all.

How can I partitioning table without closing all scheduled tasks?

I try it

BEGIN
dbms_redefinition.start_redef_table(
uname => 'kiku',
orig_table => 'pletest3_pn_kiku_api_db',
int_table => 'pletest4_pn_kiku_api_db');
END;

ERROR::

Error report:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.


texts: What does the base64 BINLOG statements in mysqlbinlog output mean?

texts:
I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0.

When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed those base64 statements:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111219 19:25:06 server id 395220  end_log_pos 107      Start: binlog v 4, server v 5.5.18-log created 111219 19:25:06
BINLOG '
ko/vTg/UBwYAZwAAAGsAAAAAAAQANS41LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#111219 19:25:06 server id 370534  end_log_pos 171      Query   thread_id=387276        exec_time=73680 error_code=0

When I've checked the MySQL 5.0 bin-log files, I've noticed those statments were missing. My first guess was that this is a new feature in MySQL Replication. I know I should not use binlogs from MySQL 5.5 on MySQL 5.0. Therefore, the only way I can promise the rollback of the data is by running the queries as queries.

When I ran mysqlbinlog --base64-output=NEVER, I noticed that the base64 statements had disappeared. I would have expected them to be translated to plain text queries, but this was not the case.

Can someone put more light on what those base64 statements means?

Thanks,

Shahar.


texts: Is it possible to have change tracking ONLY on a replication server, not original?

texts:
I am working on a system where we are pulling records from a replication server and need to know when changes have occurred.

We would like to not have to touch the original server and only read from it, including not being allowed to enable change tracking.  Can this be done?

If need be, we will simply pull all Record IDs every night and compare the two but it's certainly not the ideal solution considering we have tens of million of records.

I have seen this question:

Is it possible to use Change Tracking on a Replication target?

The answer to it seems to be suggesting that the way to do this is to enable change tracking on the original server that is then replicated, which in my case is not an option.


texts: Get a list of categories per row using a MariaDb Select Subquery

texts:
I am new to MariaDB,

I have a list os stores that are categorized by one or more categories.

I need to show a Json representation of this structure.

How can i, using Maria DB, get a list of categories in this format?

{
    store_name: "One Store",
    site_url: null,
    categories: [
        {
            name: "Category 1",
            name: "Category 2"
        }
    ]
}

I try using a select subquery but the error message is : subquery returns more than onem row:

SELECT
    store_name
    site_url
    ( SELECT a.name
        FROM  stores l, store_activity la, activities a
        WHERE la.idloja = l.id
        AND a.id = la.idactivity
    ) AS categories
FROM stores s, stores_activities la, activities a
WHERE sa.idstore = s.id
AND a.id = sa.idactivity;

This is my data modeling:

table : stores
id | name
1, One Store
2, Second Store

table : categories
id | name
1, Category 1
2, Category 2

table : store_activity
id | idactivity | idstore
1, 1, 1
1, 1, 2
2, 2, 1


texts: Delete from table one row with 2 variable with limit

texts:
I'm developing a PHP script who store session of users in a Database.

When an user logout from server, remove only one row ( because same user can login more than one time )

When server reboot, remove all session of all users from that server with same ip.

Table structure:

Table name:

totalconcurrent

Column in table:

ID [int, autoincrement, 11]
serverip [mediumint]
userid [text]

Running on :

mysql  Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1

Case: User logout

Query runs correctly but doesn't delete anything.

    elseif ($_GET['status'] == "logout"){
        $sql = "DELETE FROM totalconcurrent WHERE (serverip,userid) IN ((INET_ATON('".get_server_ip()."'),'".$_GET['id']."')) LIMIT 1;";
        if ($conn->query($sql) === TRUE) {
            echo "1 Session of ".$_GET['id']." removed";
        } else {
            echo "Error: " . $sql . "
" . $conn->error;
        }
    }

Case: Server Reboot

Query runs correctly but doesn't delete anything.

    elseif ($_GET['status'] == "reboot"){
        $sql = "DELETE FROM totalconcurrent WHERE serverip IN ((INET_ATON('".get_server_ip()."')));";

        if ($conn->query($sql) === TRUE) {
            echo "Server rebooted, removed all session stored in this server";
        } else {
            echo "Error: " . $sql . "
" . $conn->error;
        }
    }

Problem:

I've tryed many times and many types of query to do this but without finding the correct way to do this.

What queries i need for do this?


texts: Log all accessed objects during a certain time period

texts:
We are running quite a few tests for our software that require a certain set of databases. These databases are usually huge, while most of the data in there will not be used by any testcase.

In order to reduce stress on our database server due to many CI tests, we would like to remove the unused data and it would be great if that would be possible in an automated way.

Here I'm thinking of something like "Run the tests and log which objects (e.g. Table and PK) are accessed (SELECT, UPDATE, DELETE)". Then afterwards we could remove all which haven't been accessed. Is there a way to collect this information in SQL Server 2016?

Thanks, Alex


texts:
I have a problem. I have a Windows user [domain\user] on my instance that has access to some database. I want to change the default schema for that login. I can do this when I create some test login and user in the database but not for my Windows user:

ALTER USER [MFG\giorgi.nakeuri] WITH DEFAULT_SCHEMA=[TBBS]

...results in:

  Cannot alter the user 'MFG\giorgi.nakeuri', because it does not exist
  or you do not have permission.

But when I am trying to add it:

CREATE USER [MFG\giorgi.nakeuri] FOR LOGIN [MFG\giorgi.nakeuri]

I get:

  The login already has an account under a different user name.

When I observe login in properties it has a check on the database and both user and default schema are dbo. But I can not change any of those from that window. I am connected to instance with this windows user and login server role is all: public, securityadmin, serveradmin, setupadmin, sysadmin.


texts:
I am planing to upgrade my database. I am referring to this website:
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/t0007193.html

On the Before you begin part:
The second statement is Ensure that all the local databases that you want to upgrade are cataloged.

As my understanding, database is auto cataloged once its created. I run db2 list database directory and get some info, but not sure its consider cataloged or not:

[db2inst1@localhost bin]$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Kindly advise.


texts: Oracle cluster top activity class "cluster"

texts:
When under high load (many queries in parallel) our Oracle RAC gets in a state where most of the activity is reported as "cluster" by Enterprise Manager.  It stays like that for 30 minutes to a couple of hours, and then just recovers by itself and happily proceeds.

Sometimes instead of "cluster" the activity is reported as "configuration" or "concurrency".

I'm looking for any advice on how to go about diagnosing what is going on (and how to make it stop doing it). The graph shows only one node in question.  Queries are run on a service which is limited to that node.


texts: Increment value in row if it exists, otherwise create row with value?

texts:
Below is the MySQL table layout that I'm working with.  Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website.  My question is how would I go about having a new _views row for instance created upon the first visit to ID: 1106 or 1107 with a MySQL insert?

ID          key         value
-----       -----       ------
1104        _reserve    10000
1104        _views      100
1104        _watchers   2
1104        _inquiries  1
1105        _reserve    1500
1106        _reserve    24000
1106        _views      236
1107        _reserve    45300