texts: Return the first row of a group in MySQL

texts:
This query gives me the 50 closest addresses around a point:

 SELECT
     company_has_address.companys_id,
     (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-      86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
 FROM
     geocodes
         LEFT JOIN
     company_has_address ON company_has_address.address_id = geocodes.address_id
 WHERE
     company_has_address.companys_id != 5884
 ORDER BY distance ASC
 LIMIT 50

Now, to make this useful, I need to join two tables: company_has_address, which is essentially a tuple of id, company_id, and address_id,

and then join (again) the company table.

This all works. Kind of.

The problem I have is that I want a list of the 10 closest DISTINCT company names even if a company has more than one address.

So, let's say company ABC has two addresses - a billing address and a shipping address. They are next door to one another. They show up TWICE in the first query.

Now, prior to MySQL 5.7, I could use GROUP BY to (randomly) pick one of the addresses and it would just give me one result. That worked great. But, now with 5.7, this no longer works, and produces a row for each of the addresses.

Question: how do I filter the first join (company_has_address) to only take the first company_id listed there so that when I join the company table, I can get unique listings? (I don't care if it's the shipping or PO address. They are - geographically - in the same place so which address it chooses is irrelevant).


texts: Mysql database replication with geo-distributed servers

texts:
I am just a developer, who doesn't have deep knowledge in DBA. So need experts suggestions in this.
I have two  offline databases in two cities, I want to make a synchronization between two databases using a third database which will be stay alway online in a live server.

My problem is like this,
Location A database is in New York.
Location B database is in Canada.
And they are in local machine, so no connection between them. Now I need to show the changes of Location A's to Location B and Location B's to Location A. Yeah this can be solve by MASTER-MASTER replication. Also i have found a way to solve autoincrement indexes issue, by separating index numbers in different servers.

But I need another live server C where Location A's and Location B's change will reflect. So some users need not to access Location A or Location B for looking into changes. I do not also want's to use RING replication. because if one goes down then rest of the RING will be broken.

In summery I want to make the replication like this

Need help about replication process.

Thanks in advance,

Nixon


texts: Validate a column to accept only certain age limit from birthdate given using check constraint

texts:
A business rule required my table to only registrater  certain dob from bithDate column to be inserted or allow. I created the check constraint but i kind of having logical error i not able to spot out. pls advise

ALTER TABLE Personalinfo
ADD CONSTRAINT chk_ValidDOB   CHECK
(
CASE
    WHEN (convert(int,DATEDIFF(M, BirthDate, getdate())/12) BETWEEN 30 AND 33) THEN 1
    ELSE BirthDate
END = 1
);
Error message:  `Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "chk_ValidDOB"`


texts: Get User IDs with no boolean true answer

texts:
I'm having difficulty wrapping my head around this query. I have a table with a bunch of user features, and I'm trying to get all the users that have no features turned on. I know I need to do something with COUNT and GROUP BY, but SQL is really not my strong suit, and this needs to be done sooner rather than later, otherwise I'd do more research on my own. This is the table structure:

| UserFeatureID | UserID | FeatureID | Active
-----------------------------------------------
| 1             | 1      | 1         | false
| 2             | 1      | 2         | true
| 3             | 2      | 1         | false
| 4             | 2      | 2         | false
| 5             | 3      | 1         | false
| 6             | 3      | 2         | false
| 7             | 4      | 1         | true
| 8             | 4      | 2         | true

So the list of user ID's it'd return is 2 & 3 since they are the only ones that have all features turned off. Any help is appreciated, thank you!


texts: Failed attempt to fetch logical page - tempdb

texts:
At least 2 times a week I get an error as the tempdb should be corrupt.

  Failed attempt to fetch logical page xxxx in database 2.

This on all my 8 SQL Servers 2008 R2.

The strange thing is that after the error, users can continue to work without any problem. And using the corrupted tempdb for months... I don't find any topics where you talk about this, is this a bug? Or real corruption?

I found as only solution people reccomend restarting the service, something I don't want to do.


texts: How to split list of values into multiple rows after semi-colon?

texts:
I'm thinking this is easy, but I'm having a hard time manifesting. I have a list of values, e.g.
@bob
@sarah
@kelly;john
@denver_colorado
@james12;kellybelly
I want the list to be:
@bob
@sarah
@kelly
@john
@denver_colorado
@james12
@kellybelly
essentially, splitting a line after ";" and add @ as a prefix to all values


texts: Count(*) as rolecount, filter by rolecount = 1

texts:
How can i filter out the rows who only got rolecount = 1?
WHERE rolecount = 1 gives an error. Can't really figure out how to do it.

SELECT
u.uid AS uid,
u.name AS name,
u.picture AS picture,
n.field_full_name_value AS field_full_name_value,
f.field_function_value AS field_function_value,
pi.field_phone_intern__value AS field_phone_intern__value,
ur.rid AS rid,
COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u
LEFT OUTER JOIN `dev_drupal_project`.users_roles ur ON u.uid = ur.uid
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_full_name n ON u.uid = n.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_function f ON u.uid = f.entity_id
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_phone_intern_ pi ON u.uid = pi.entity_id
GROUP BY u.uid;


texts: mysql - Do DDL statements replicate under Row Based Replication if the Default Database is not set?

texts:
In Statement Based Replication the Default Database is of paramount importance.

e.g.

replicate-do-db=dbTwo

USE dbOne;
UPDATE dbTwo.tableTwo . . . .

will fail as dbOne has been set as the default database.

Whereas under Row Based Replication it should be fine as it looks at the database that is changed rather than the default.

https://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

However, with Row Based Replication all DDL statements are replicated as Statements.

https://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

  With the binary log format set to ROW, many changes are written to the
  binary log using the row-based format. Some changes, however, still
  use the statement-based format. Examples include all DDL (data
  definition language) statements such as CREATE TABLE, ALTER TABLE, or
  DROP TABLE.

So in that case does the default database come into play.

e.g.

replicate-do-db=dbTwo

USE dbOne;
ALTER TABLE dbTwo.tableTwo . . . .

will that fail to execute as dbOne is the default, or will it succeed as it is dbTwo that has been changed?

All the examples I can find refer to UPDATE statements, and are quite clear on what will and wont work.

But I can't find any examples that refer to DDL statements using Row Based Replication.

(n.b. I'm currently using MySQL 5.5 in case that makes a difference - upgrading is not an option any time soon)


texts:
I have users (developers) in Vertica who need to create tables for testing purposes, which will later be deployed under specific production schema.
I do not want them to have the ability to create tables in the production schema though.

Im looking for the grants necessary to allow a user the ability to create tables.
Do most environments create a schema for each user and then grant authorization for the user on that schema or what is the best practice?


texts: Joining Three Tables by using IDs as Foreign Keys

texts:
I'm starting out with learning MySQL and so far I realized I need to JOIN three tables.
The three tables are

Users

Groups

Members
There is a list of users, a list of groups that users can join, and then the members table is a many-to-many relationship between Users and Groups that lists which user joined which group.

I have a user_id auto_increment for the user table, and a group_id auto_increment for the groups table.
In my Members table, I have user_id and group_id which are both primary keys and  foreign keys to the respective tables.
Because my foreign keys are IDs (Integers), If I wanted to display a list of users in a certain group by username, I would have to use JOIN since my Members table only references ID integers...
I would appreciate a professional opinion on whether I'm on the right path and if it will be manageable in the future so I won't have to restart from scratch.
I read online that using IDs is good because sometimes using other columns like usernames are vulnerable to change and querying integers is much faster. Is this what most of you guys do? It just seems like a lot of work to have to JOIN lots of tables together.


texts:
I'm thinking about how to hide as many attributes of relations from a user as possible & if functions can help with that.
Example:

The user "dummy_user" should have as little insight as possible.
The table "user" contains user data incl. password. The password should not be accessible for the dummy_user.
The function "does_password_match(user_id, password)" checks if the password matches the user.

My question now: I read in the PostgreSQL doc: "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it.".  So that means I can create the function does_password_match as admin and it can access the password that way and the user is allowed to execute it with the privileges of the admin?
But the dummy_user can still see the body of the function. So you can't really hide an internal structure this way, right?
And if within this function the password is searched via a SELECT, can the dummy_user then potentially still see the value of the field? And so the password of a user can potentially still be read?


texts: How do i prevent executing os command in oracle 11g

texts: I have Oracle 11g installed on a linux serveur and i'm looking for a way to prevent sysdba from executing system command through oracle(Using java for example).


texts: Locking the database to a single user so that other user wont change it

texts:
I am using SQL Server CE 4.0. In office the database (.sdf) file are on a server. When I want to change design of the database I have to copy it to local PC and access it since n/w connection to a compact database not allowed.

But when I modify the design of tables I have to be sure that no other user in my office connects to that database and changes the same database. Because I have to again copy the changed database to my server PC and I don't want the design changes made by others. So how can I be sure that no one else connected to the database which I have been editing. Is there any method of locking the database to a single user when I edit the design of the database.


texts:
We have some complex query that was very slow. I managed to reduce the query to a simple reproduction. It seems that the combination of greatest and log is the cause, but I don't understand why.

Here is a full sql-fiddle example to run the queries - and you can also View the execution Plans of the queries (press the link at the bottom of the query result on the sql-fiddle page)

So here is the slow query:

select count(value)
from (
         SELECT  log(greatest(1e-9, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

We just generate a series of 20k numbers and use log(greatest()). This query takes about 1.5 seconds.

I thought that calculating the log may take long, but the following query is also fast (~5ms):

select count(value)
from (
         SELECT  log(x) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

Just as a test I exchanged greatest and log - this is also fast (~5ms):

select count(value)
from (
         SELECT  greatest(1e-9, log(x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

The QUERY PLANS for all 3 queries are the same:

Aggregate (cost=22.51..22.52 rows=1 width=8)
-> Result (cost=0.00..5.01 rows=1000 width=4)

Can anyone explain why the first query is so slow - and maybe anyone knows a workaround?

More details

slow platforms

I get similar results on all of these (first query is a magnitude slower):

SQL Fiddle uses pg 9.6
my local PC with similar results: Win10 64bit, pg 11.5 running in Docker
remote server: Ubuntu 18.04 64-bit running pg 11.5 in Docker
rextester.com

slow query ~ 3sec
fast query ~ 0.5sec

count

When I change count(value) to count(*) or count(1) (number one) the query is fast

but this does not help me because the production query does not even include a count
anyway, I wonder why there is a difference in this case (there are no null-values in the data)


texts: changes on multimaster replication and activate GTID

texts:
I've been reading a lot about the changes I planned to do to our DB landscape
but I need some confirmation or recommendation to be sure the work I'll do goes without causing a disaster.
Here is what we have on all hosts:

debian8
MySQL server version: 5.7.16-log MySQL Community Server (GPL)

Replication scene all anonymous:
Master               Slave

S3 some DB's       > S4 on channel1
S2 one 150GB DB    > S4 on channel2
S2 one 150GB DB    > S5 one only channel

All replications work well and are max a few sec. behind master. The replicated DBs I use to do backups.
The 150GB DB on S2 is HA and heavily accessed, but it's possible to shut down for let's say < 10min. S3 DB's are not that critical but a < 10min downtime is highly appreciated.
The bigger picture is, S2 is an old machine and needs to go off grid. I like to have the 150GB DB on S4 and S5 in circular replication in order to do load balancing, failover and backups. S3 DB's needs to be replicated to one of S4 or S5 to do backups.
While that changes I like to change all replications to GTID. That change to GTID is the point I'm worried about.
Here is how I think I have to do all that:

on S2 take the services off that access the DB

take all S2 - S5 read only  SET @@global.read_only = ON;

show slave status on S4 and S5
Q: what do I have to watch for before I shutdown the server?

Shutdown all servers

change Mysqld section in config file
S2, S3, S4, S5
gtid-mode = ON
S4, S5
log-bin   = mysql-bin
server-id       = xx

Start S3

Start S4 with --skip-slave-start

Start S5 with --skip-slave-start

try to connect with replication user across all machines as needed

S4 and S5 Change master to ... the corresponding machines with
corresponding user, password, channel and
MASTER_AUTO_POSITION = 1;

Start Slave for channel1  on S4

Start Slave for channel2  on S4

watch logfiles

Start Slave for channel1  on S5

if logfiles doesn't show any errors
restart all servers

make the services that use DB's point to new Servers

I hope I got all together. Any recommendations highly appreciated.


texts:
I have the following query written into the slow log (mysql-slow.log).

I don't have much idea how to optimize this query. I already have indexes for:

mode,
f.user_id... etc

but doesn't seem to be helping me. Mind you, the table Engine is InnoDb.

# Query_time: 10.830652
    Lock_time: 0.000058
    Rows_sent: 1
    Rows_examined: 18194
    Rows_affected: 0
# Bytes_sent: 75
SET timestamp=1439531119;
SELECT
  COUNT(u.user_id)
FROM (
    users AS u
    LEFT JOIN photos AS f
        ON u.user_id=f.user_id
        AND f.main='Y')
WHERE  u.mode NOT IN('suspended','deleted','hidden')
AND u.activated='Y';


texts: SQL Server replicating data to an archive

texts:
I have the following scenario:

Multiple SQL Servers where production machines log about 80GB of data (mostly images) daily in a database (each machine has a server with one database). Since these servers have about 4TB of space the amount of days they can store is limited. So the data on these servers is overridden frequently. But we want to make reports, that date back to up to 2 years.

I could code stored procedures to fetch all new data entries and copy them to a backup SQL Server which has more space every 2 hours or so. But I am talking about a database with approximately 25 tables and I think there might be a better way.

Do you think transactional replication or log shipping might be better options in this scenario?

If the SQL Server on the production site roles over the data (I have not yet had the opportunity to check how this actually works on the systems), I don't want the backup server to replicate this. The Backup server should keep all records and only add new ones.

Any advice on this would be appreciated.


texts: Transfer Data from One Table to Another or else Update the Data

texts:
Good Afternoon. I have two Tables and they are

Table: GeneralInventory

  WareHouseID | ItemCode | Qty
  Null          Null       Null

Note: as of now it is empty

and Table: Receiving

   RINo     |  ItemCode | ReceivedQty
    RI805ZFJ   FBMURG5WR   1.00
    RI467RGR   FBMURG5WR   1.00

As you can see this formats on Table are most likely you will see in Inventory System, My Table GeneralInventory is the Main Inventory where all Items that you will received will transfer here and that Table is Receiving as what you see in the table Receiving there 2 Data and they are.

Please be focused on the RINo field they are Different right? now here is what I will do and also my Question (I will convert my Question in Step by Step procedure of the Program)

The Program will Select 1(One) Data from Table Receiving
I will click a button named Post and the Program will do the Following

-Check if the ItemCode of the Selected RINo already Exist in the Table                              GeneralInventory and If the ItemCode  is already in there then ReceivedQty of table Receiving and Qty of Table GeneralInventory will sum up or else add the Data.

I hope you get my point but if not I will show you further Example.

In General: If the Data Exist then Sum it Up or Else add the New Data.

I am using this Code.

INSERT INTO GeneralInventory (ItemCode, Qty)
SELECT RE.ItemCode, RE.ReceivedQty
FROM Receiving RE LEFT JOIN
     GeneralInventory GI
ON   GI.ItemCode = RE.ItemCode AND GI.Qty = RE.ReceivedQty
WHERE RE.RINo = 'Data of RINo'

UPDATE GeneralInventory GI
INNER JOIN receiving RE
    ON GI.ItemCode = RE.ItemCode AND GI.Qty = RE.ReceivedQty
SET GI.Qty = GI.Qty + RE.ReceivedQty RE.RINo = 'Data of RINo'

TY for future Help


texts: We argued in our DBA team on whether an Oracle Database with SecureFiles LOBs needs to be an Enterprise Edition. Can I use Oracle Secure File Lobs in a Standard Edition?


texts: Find least recently used mysql table index

texts:
I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables.

While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if there is a tool that could help me find out least recently used indices from all the tables.

For eg. , if table "A" has three indices defined "index_1", "index_2" and "index_3", and out of them "index_3" is used least frequently , assume its used every 1/10000 queries made on the table, then the output of the script or tool should be "index_3".

Is there a good way or a tool that could help me run this analysis on the database?

Thanks in advance.


texts:
I have a postgresql database on / root partition (61GB used out of 63GB), so I asked the VPS provider to add more disk (50GB to /mnt/vdb1)

How to add another data directory to that new partition (using symlink or something)?

So that newly created tables does not use /var/lib/postgresql/10/main/base but use /mnt/vdb1 instead. I've been looking but it seems multiple data directory only available on old PostgreSQL 6/7?


texts: Is there any way to recover lost table data on sql server without backups?

texts:
Unfortunately some tables are completely deleted with important data. The database is hosted on azure, but there is no back up for past 1 month as well.

Is there any way I can get the lost data?
The database is azure SQL v 11.


texts: Partition Function with only one value, what does this effect?

texts:
I am currently looking at a database that has been setup by the default installation routine of AppFabric. Looking at the creation scripts I encountered this partition function:

CREATE PARTITION FUNCTION [MSAppServerPF_ASWcfEventsTable](datetime2(7)) AS
RANGE RIGHT FOR VALUES (N'0001-01-01T00:00:00.000')
GO

And it is being applied to a table that way:

CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS
PARTITION [MSAppServerPF_ASWcfEventsTable] TO ([PRIMARY], [PRIMARY])
GO

I wonder what this specific partition style effects in. I was under the impression that you store boundary values into the partition function by which the data will be partitioned.
Unfortunately, looking for this on the web I could not find a similar example using just one DATETIME2 value in the partition function definition.

Can someone shed some light on this for me?


texts:
I have following table, which does have an UUID as primary key, which also acts as foreign key.

CREATE TABLE `xxx` (
    `guid` char(36) NOT NULL,
    ....
    PRIMARY KEY (`guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT ;

CREATE TRIGGER before_insert_xxx
  BEFORE INSERT ON xxx
  FOR EACH ROW
  SET new.guid = uuid();

Every time when I insert a new dataset, I need to know which UUID was generated, so I can use this UUID for references. Alas, LAST_INSERT_ID() does not work.

INSERT INTO `xxx`(...) VALUES (...);
SELECT LAST_INSERT_ID();

I also tried to modify the trigger like this:

CREATE TRIGGER before_insert_xxx
  BEFORE INSERT ON xxx
  FOR EACH ROW
  SET new.guid = LAST_INSERT_ID(uuid());

But it doesn't help either, and other people have the same problem.

A few solutions I can think of, but I don't like them very much ...

Solution A:

Generate the UUID by the application which writes the data. Not a good solution, because:

it is hard to generate a real UUID (which fulfils all criteria of RFC 4122) in PHP.
Also, I want to ensure (directly in the DBMS) that people get an unique UUID.
With the trigger, I cannot use a custom UUID in the INSERT statement

Solution B:

Add a INT primary key and let GUID be UNIQUE only.
After inserting, receive the INT primary key with LAST_INSERT_ID
Run a SELECT statement, which searches the GUID for the given INT primary key.

Since I want to insert a lot of datasets in short time, having a SELECT after each INSERT would make the progress much slower, since the amount of queries doubles.

Is there an acceptable solution C?


texts: Why have SQL Server services disappeared from services.msc?

texts:
I'm running an Always On Availability Group with 2 SQL Server 2014 database servers in an AD domain. Updates are supplied to all domain servers via WSUS. Also have a third MS Reporting Services server that is not part of the group.

This morning, with DB01 as primary and DB02 as secondary, I installed Microsoft SQL Server 2014 (SP2-CU12) (KB4130489) - 12.0.5589.7 (X64) on all three servers. Went in ok on the report server and DB02, Failed to go in on DB01 (error 86D80017). Instead of troubleshooting the error, I decided to manually install KB4130489. Manual install on DB01 went ok. AOHA Dashboard shows all is ok. What is weird is that on DB01, the Services Management Console UI does not show any of the expected SQL-Server related services (5 of them in my case). SQL Server Configuration Manager shows services (as expected - see screen shot below). They just don't show up in services.msc

Why have SQL Server services disappeared from services.msc on DB01?

SQL Server Configuration Manager shows services (as expected)

However, services.msc does NOT show services as expected (they should appear right after "Spot Verifier" service


texts: MySQL, can't see my schema's tables in the navigator

texts:
i'm new to MySQL.
I just created a database and i do not get problem while running it.
The problems come when i want to query it so i create another connection (?) but the navigator on schemas doesn't see any tables, or in the best case just the first one.
Yes i did refresh it a plenty of time.
Thank you anyway and have a nice day.


texts: DB2 Audit Policy not producing EXECUTE data

texts:
I'm trying to set up an Audit facility on a DB2 AWSE V10.5 FP5 database

running on Red Hat Linux Server 6.8.

The definition for the policy is :

ALTER
AUDIT POLICY Failures
    CATEGORIES
        Audit STATUS Failure,
        Checking STATUS Failure,
        Context STATUS Failure,
        ObjMaint STATUS Failure,
        SecMaint STATUS Failure,
        **execute with data STATUS Failure**,
        Validate STATUS Failure
    ERROR TYPE NORMAL ;

This is producing data for all the categories, except EXECUTE.

My test script includes statements that fail with SQL0204, SQL0206, etc. but no data is extracted into the execute.del file in the audit output.

Anyone have an idea what I'm missing?


texts:
I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results.

Table "job" : parent_id, target_id, start_date, end_date, jobposition_id

Table "study" : parent_id, target_id, start_date, end_date

At this moment if I do something like :

SELECT *
FROM user AS u
LEFT JOIN study AS s ON s.parent_id=u.id OR s.target_id=u.id
LEFT JOIN job AS j ON j.parent_id=u.id OR j.target_id=u.id
WHERE u.id=2;

I got a resultset with every common column doubled (I have 2 times parent_id, target_id, start_date, end_date).

How can I get a simple resultset with only one column parent_id, etc (and a jobposition_id column set to NULL for every study result) ?

Thanks


texts: Deploying Ola Hallengrens scripts - Index Optimization

texts:
Never before I used Ola Hallengren Scripts .

I am planning to deploy the Ola Hallengren scripts for Index Optimization for the first time. I need to deploy them in PROD so I want to be more careful. Anyhow will first test in Dev environment. But please help me understanding the important things to keep in mind before deploy OH Index optimization scripts.

I copied the media from link and below commands :

As I mentioned I never deployed the OH scripts before.

Can anyone who already deployed and successfully using the OH Index Optimization scripts guide me on below:

Important parameter Values which I need to modify?
Where will these commands create the SP and related Command Execute and Command Log, all in Master Database or MSDB?
Series of code execution - Which code I need to execute first?
What is command Log here?
We are with AOAG set up on SQL Server 2014. Please guide me keep this in view.

NOTE : We are using AOAG 2014.

Please help me keeping this in mind. I can first test in Dev and later deploy in PROD.


texts: MySql Very Slow Writes & Out of memory error

texts:
I am having a rds db instance in AWS which is used by Drupal 7 instatllation.
I am getting the below error messages in the log:

> 130912 11:57:39 [ERROR] /rdsdbbin/mysql/bin/mysqld: Out of memory
> (Needed 26843536 bytes) 130912 11:57:39 [ERROR]
> /rdsdbbin/mysql/bin/mysqld: Out of memory (Needed 26843536 bytes)
> 130912 12:35:47 InnoDB: cannot calculate statistics for table
> m1newdb/cache_image InnoDB: because the .ibd file is missing. For
> help, please refer to InnoDB:
> http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

And another error like this:

> /rdsdbbin/mysql/bin/mysqld, Version: 5.5.31-log (Source distribution).
> started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id
> Command Argument /rdsdbbin/mysql/bin/mysqld, Version: 5.5.31-log
> (Source distribution). started with: Tcp port: 3306 Unix socket:
> /tmp/mysql.sock Time Id Command Argument /rdsdbbin/mysql/bin/mysqld,
> Version: 5.5.31-log (Source distribution). started with: Tcp port:
> 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument
> /rdsdbbin/mysql/bin/mysqld, Version: 5.5.31-log (Source distribution).
> started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id
> Command Argument /rdsdbbin/mysql/bin/mysqld, Version: 5.5.31-log
> (Source distribution). started with: Tcp port: 3306 Unix socket:
> /tmp/mysql.sock Time Id Command Argument

My parameter that is set is as per below:

default_storage_engine  = innoDb
innodb_additional_mem_pool_size  =  52428800
innodb_io_capacity = 10000
innodb_open_files = 120
max_allowed_packet = 33554432
max_connections = 3000
max_heap_table_size = 134217728
query_cache_size = 2147483648
read_buffer_size = 52428800
slow_query_log = 1
tmp_table_size = 134217728
binlog_cache_size  =  32768
innodb_buffer_pool_size  = {DBInstanceClassMemory*3/4}
innodb_file_per_table = 1
innodb_io_capacity = 10000
innodb_log_buffer_size = 8388608
innodb_log_file_size  =  134217728
key_buffer_size      = 16777216
log_output  =  TABLE
max_binlog_size  =  134217728
read_rnd_buffer_size  =  524288
thread_stack =  262144
read_only = {TrueIfReplica}

The site loads fast, but adding one article takes more than 2 minutes rendering the site unusable.

Instant Size:

  M1.Medium.
  HDD Size: 10GB
  Memory : 4 GB
  CPU : 2 Compute Unit`


texts: My stored procedures often get invalidated

texts:
The reason for invalidation should be because of some DDL activity, but I don't have any DDL in the SPs. I need to find what is that DDL statement.

If the object is in an invalid state, when you call them it will change to valid and do the execution. In my case I only get the desired result if I recompile the invalid object. Why doesn't just calling the SP validate it?

I was told if there are compilation errors, then just calling the SP won't bring it to valid. If there are compilation errors then how come just recompiling fixes it? Compilation errors are caused by some syntax errors.

what are the causes of invalid objects?


texts: SQL Server: Covering indexes including all columns?

texts:
Our team has inherited an application and associated database. The previous developers appear to have enforced a rule where every index, on every table, has an INCLUDE clause to always add every column that isn't otherwise part of the key. These tables have on average anywhere from two to five indexes or unique constraints as well as foreign keys.

The intent looks to be to improve SELECT performance regardless of what query is thrown at the database, as access is via an ORM that by default (but not always) retrieves all columns. We expect that the side effects of this are increased storage requirements (possibly significantly so) and additional overhead time for INSERT/UPDATE/DELETE.

The question is, is this a sensible strategy? Our team has history with SQL Server but no members who would consider themselves experts on its internal behaviour (though the question has been raised that if this strategy was optimal, wouldn't it be the default by now?). What other side effects (database server CPU/memory/TempDB usage, etc) should we be expecting, or are some of our assumptions above incorrect?

Additionally, the application can be installed into both SQL Server on-premise (versions since 2012), as well as Azure SQL -- should we be prepared for any differences between the two, or additional side effects on Azure, as a result of this approach?


texts: How would I build a data model for parking lot hours and respective parking fees?

texts:
Say a parking lot has the following hours and associated costs...

Mon-Fri 7am - 5pm parking is $5/2hrs
Mon-Fri 5pm - 7am parking is $1/hr
Sat and Sun - 12am - 12pm parking is free

How would you model this data such that I can ask questions like "What does Parking Lot A cost right now?"

I'm not sure at the moment, but it seems like a model should accomodate multiple prices for certain times of the day. For example...

Mon-Fri 7am - 5pm parking is $5/2hrs or $7/3hrs

It should also be easy to modify if, for example, a lot's cost ever changes.

Here's a general idea I had so far...

ParkingLot -
    ParkingLotId
    Name
    Address

ParkingLotSchedule -
    ParkingLotId
    DayOfTheWeek
    StartTime
    EndTime
    Cost

Then I could basically write a query something like...

select cost from parkingLotSchedule where parkingLotId = currentLotId and dayOfTheWeek = today and currentTime between startTime and endTime

This doesn't really take into account the possibility of multiple prices for certain times (granted I'm not 100% sure that's necessary yet). It also seems like there could potentially be a lot of records for a single lot.

Is there a better way to do this? If so, what would a model look like?


texts:
We have a server running single instance of SQL SERVER in production environment.

We are planning to create another Instance of SQL SERVER in same server.

My question do we have to buy a new license or the existing license is good enough ?


texts: Properly determine SQL Server version and update level

texts:
I have some existing code which tries to read the current version of SQL Server that's installed for a particular instance name by using a WMI query.  It checks the first dotted part of the VERSION (to trigger an Upgrade install) and the SPLEVEL (to trigger a Patch install).  This has worked well up to now.  (I need to be able to get the version programmatically and without actually opening an SQL connection to the instance.)

However 2017 and 2019 have done away with service packs, rendering SPLEVEL useless, so I'm looking for an alternative to determine when only an older CU is present.

According to the table of version numbers, on my fresh 2019 install with CU4 applied, I should be seeing a VERSION of 15.0.4033.1 and a FILEVERSION of 2019.150.4033.1 (the actual page has a typo in the latter).

However while I am seeing the matching FILEVERSION, what I see for the VERSION is still the RTM value of 15.0.2000.5.  This is also confirmed if I look at the Advanced Properties in SQL Server Configuration Manager.  (And FWIW I also confirmed that SPLEVEL was still 0.)

Oddly, if I connect using Management Studio then it does show the version as 15.0.4033.1.

I don't mind switching to comparing the FILEVERSION if that's what's needed, but what has me concerned are the "GDR Builds".  Looking at the equivalent table from 2017, there doesn't appear to be any particular rhyme or reason and a more recent GDR build can have a lower version number than an older CU build.

How can I make sense of this?  What even is a GDR build and where does it come from anyway?  Is it safe to assume they'll never "upgrade" an install that already has some CUs applied?


texts: Managing multiple postgres versions on CentOS

texts:
I have the following packages installed with yum:

Installed Packages
postgresql92.x86_64                                                                                9.2.24-1.65.amzn1                                                                                @amzn-updates
postgresql92-libs.x86_64                                                                                9.2.24-1.65.amzn1                                                                                @amzn-updates
postgresql92-server.x86_64                                                                                9.2.24-1.65.amzn1                                                                                @amzn-updates
postgresql92-server-compat.x86_64                                                                                9.2.24-1.65.amzn1                                                                                @amzn-updates
postgresql95.x86_64                                                                                9.5.10-1.77.amzn1                                                                                @amzn-updates
postgresql95-libs.x86_64                                                                                9.5.10-1.77.amzn1                                                                                @amzn-updates
postgresql95-server.x86_64                                                                                9.5.10-1.77.amzn1                                                                                @amzn-updates

postgresql92 was first installed and started with sudo service postgresql start.

postgresql95 is subsequently installed but I can't seem to control it individually:

/home/ec2-user$ sudo service postgresql95 stop
Stopping postgresql95 service:                             [  OK  ]
/home/ec2-user$ sudo service postgresql95 status
postmaster (pid  43442) is running...
/home/ec2-user$ sudo service postgresql92 status
postmaster (pid  43442) is running...

Why are they having the same pid?

Also I only have 1 configuration file, located in /var/lib/pgsql9/data/postgresql.conf.

The OS is Amazon Linux AMI release 2017.03.


texts: Setting up a simple "permissions architecture" in PostgreSQL

texts:
I host the databases of several clients. For each database dbname, I currently have a dbnameusers role that I grant to the users of that client.

I would now like to have read only "sub roles".

I created a test database, a role usersro and a user ro (in role usersro).

Then I granted all privileges on the DB to usersro and revoked the CREATE privilege :

# grant all on database test to usersro;
GRANT
# revoke create on database test from usersro;
REVOKE

\l shows something that looks correct :

# \l
                            List of databases
    Name    |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
------------+-------------+----------+-------------+-------------+-----------------------
 test       | cat         | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/cat              +
            |             |          |             |             | cat=CTc/cat          +
            |             |          |             |             | usersro=Tc/cat

But connecting as user ro, I can still create a table. I guess this comes from the PUBLIC role but I haven't found a way to see those default privileges.

My guess is that I should revoke all privileges from the PUBLIC role and recreate them in the usersrw role and only part of them in usersro (hence the need to know which are the default ones).

Is this the correct approach ? I have the feeling that PostgreSQL is quite permissive out of the box and that you have to break everything in order to build a multilevel access architecture.


texts:
I have a two node RAC setup where my scan ips are registered in DNS and my public Ips are also registered in the dns. Now while connecting from my windows machine through oracle client in sql developer im getting connection time out error and network adapter error.
My question is that should I register my VIP also in dns and will that resolve this issue?


texts: ORA-39014 import in Oracle 19c from DMP file of Oracle12c

texts:
I have been testing out expdp/impdp of databases from 12c to 19c( newly created database) but one db have game me this error:
nohup impdp system/****@CKLPROD CONTENT=METADATA_ONLY PARALLEL=4 DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_CKLPROD_20210317_B419C.DMP LOGFILE=IMP_CKLPROD_20210317_B419C_META.log > IMP_CKLPROD_20210317_B419C_META.out &
> Processing object type
> DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE ORA-39014: One or
> more workers have prematurely exited. ORA-39029: worker 1 with process
> name "DW00" prematurely terminated ORA-39014: One or more workers have
> prematurely exited. ORA-39029: worker 2 with process name "DW00"
> prematurely terminated Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" stopped due
> to fatal error at Wed Mar 17 14:04:14 2021 elapsed 0 00:18:42

the alert log have the following ORA errors:
Line 122: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 355: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 508: ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Line 639: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 781: ORA-00313: open failed for members of log group 1 of thread 1
Line 784: ORA-00313: open failed for members of log group 1 of thread 1
Line 788: ORA-00313: open failed for members of log group 2 of thread 1
Line 791: ORA-00313: open failed for members of log group 2 of thread 1
Line 795: ORA-00313: open failed for members of log group 3 of thread 1
Line 798: ORA-00313: open failed for members of log group 3 of thread 1
Line 803: ORA-00313: open failed for members of log group 1 of thread 1
Line 806: ORA-00313: open failed for members of log group 1 of thread 1
Line 810: ORA-00313: open failed for members of log group 2 of thread 1
Line 813: ORA-00313: open failed for members of log group 2 of thread 1
Line 817: ORA-00313: open failed for members of log group 3 of thread 1
Line 820: ORA-00313: open failed for members of log group 3 of thread 1
Line 2208: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 3062: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 3900: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 4733: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 5055: ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Line 5834: ORA-07445: exception encountered: core dump [ph2csql_idndef_to_diana()+39] [SIGSEGV] [ADDR:0x0] [PC:0x55CDCD7] [SI_KERNEL(general_protection)] []
Line 5853: ORA-07445: exception encountered: core dump [ph2csql_idndef_to_diana()+39] [SIGSEGV] [ADDR:0x0] [PC:0x55CDCD7] [SI_KERNEL(general_protection)] []


texts:
I have a collection invites which i am using to store some invite data.Below are two rows of data i captured from my collection invites

{
        "_id" : "n4nECpQFnBFiKkt6N",
        "createdAt" : ISODate("2016-10-11T11:59:16.988Z"),
        "lat" : -1.2861292178882062,
        "lng" : 36.89101696014404,
        "eventop" : "0800123456",
        "eventtitle" : "test2",
        "eventtime" : ISODate("2016-10-11T11:59:16.988Z"),
        "invited" : "0800123456",
        "hour" : "0",
        "minute" : "1",
        "pmam" : "pm",
        "eventduration" : "60",
        "eventtype" : "4",
        "eventcategory" : "hackathon",
        "eventstatus" : 11
}

{
        "_id" : "LtFB4WMu4xGC7Kyxf",
        "createdAt" : ISODate("2016-10-11T11:59:16.939Z"),
        "lat" : -1.284970795243023,
        "lng" : 36.89183235168457,
        "primaryowner" : "0800123456",
        "eventtitle" : "test2",
        "eventtime" : ISODate("2016-10-11T11:59:16.939Z"),
        "hour" : "0",
        "minute" : "1",
        "pmam" : "pm",
        "eventduration" : "60",
        "eventtype" : "4",
        "eventcategory" : "hackathon",
        "eventstatus" : 11
}

I want to fetch some data from the collections invites. This is the data i want to get.

I want to get data(all fields) where invited field is 0800123456 and eventtype is 3 and eventstatus is 11. I also want to get data where eventtype is 4 where eventstatus is 4 and the field primaryowner exists.

db.invites.find({invited:"0720069005",eventtype:"3"},{eventstatus:"11"},{$and: [{eventtype: "4"},{eventstatus:"11"},{primaryowner: { $exists: true }}]}).pretty()

This is my query.It returns some data but i am not sure whether its correct.


texts: Remove 5th position character From column using MYSQL Query

texts:
I want remove 5th character from column using MYSQL Query.

My column lokks like

PRSW000000001
PRSW000000002
PRSW000000003
PRSW000000004


texts: What does HSJOIN mean in an Explain Plan?

texts:
I have the following EXPLAIN PLAN results from a query on my DB2 database.

0 SELECT STATEMENT ( Estimated Costs =  5.928E+02 [timerons] )
       1 RETURN
           2 HSJOIN
               3 [O] FETCH LTBP
                   4 IXSCAN LTBP~M #key columns:  0
               5 [I] FETCH LTBK
                   6 IXSCAN LTBK~V #key columns:  0

What does the HSJOIN on line 2 mean?


texts: How to move up not null alphabetically sorted values in MySQL?

texts:
I have a table like this:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Zoe   |NULL  |NULL   |NULL  |
   |Julia |NULL  |NULL   |NULL  |
   |NULL  |Ashley|NULL   |NULL  |
   |NULL  |Belvet|NULL   |NULL  |
   |NULL  |Maria |NULL   |NULL  |
   |NULL  |NULL  |Mark   |NULL  |
   |NULL  |NULL  |Jane   |NULL  |
   |NULL  |NULL  |NULL   |Eve   |
   |NULL  |NULL  |NULL   |Jenny |
   |NULL  |NULL  |NULL   |Ketty |

How can I order alphabetically every single column in a way that the NULL values are pushed on the bottom, like this:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Julia |Ashley|Jane   |Eve   |
   |Zoe   |Belvet|Mark   |Jenny |
   |NULL  |Maria |NULL   |Ketty |

?


texts: T-SQL: Assign identifier to block of results

texts:
I'm not sure exactly how to phrase this question but... I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each group of a the same block value in the results returned by my query.
An example might be helpful! If I have a SQL statement such as
SELECT
   Id,
   Title,
   Block,
   Order
FROM
   TableName
ORDER BY Order ASC

that returns the result:

Id
Title
BlockName
Order

100
Title A
Foo
1

101
Title B
Foo
2

102
Title C
Foo
3

103
Title D
Bar
4

104
Title E
Bar
5

105
Title F
Foo
6

Is there any fairly straightforward way to generate a result which gives a unique id to each block of results, something like:

Id
Title
BlockName
Order
BlockGroup

100
Title A
Foo
1
1

101
Title B
Foo
2
1

102
Title C
Foo
3
1

103
Title D
Bar
4
2

104
Title E
Bar
5
2

105
Title F
Foo
6
3

I hope this is clear enough :) Thanks in advance for any help!


texts: How to create a function(table_name) that return a collection in Oracle?

texts:
I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted.

I've been doing some search, many examples are of the form:

first, define a table type;
then, fill a table of that type and return it.

But I won't know the structure of the collection that need to be returned until the function is called, so I can not define a table type at the time of programming.

How can I make it?


texts:
I have a huge database of library system but I have a problem on how to design tables on this database. This is because I have under one entity (for example AUTHOR) a lot of repeatable fields. So I don't know whether to make a table with a foreign key for each repeatable data or that this will result in too many tables and may affect performance.

Taking into consideration that AUTHOR is only a small entity with about 30 columns like author or somehow bigger.

So is it suitable to make about 10 tables for author only?

I know that I will make all the Non Repeatable in one table called Author but the big problem is for the rest of fields.

For example:

Author ( Repeatable )

NOTE

(R)    ---> Repeatable

(NR) ---> Non repeatable

indicator means a character or digit which means some specific data about an author

First   Indicator Type of personal name entry element
0 --> Forename
1 --> Surname
3 --> Family name

Second Indicator Type of added entry
0 --> No information provided
2 --> Analytical entry

Sub-field Codes

•   a - Personal name (NR)

•   b - Numeration (NR)

•   c - Titles and other words associated with a name (R)

•   d - Dates associated with a name (NR)

•   e - Relator term (R)

•   f - Date of a work (NR)

•   g - Miscellaneous information (R)

•   h - Medium (NR)

•   i - Relationship information (R)

•   j - Attribution qualifier (R)

•   k - Form subheading (R)

•   l - Language of a work (NR)

•   m - Medium of performance for music (R)

•   n - Number of part/section of a work (R)    •   o - Arranged statement for music (NR)

•   p - Name of part/section of a work (R)

•   q - Fuller form of name (NR)

•   r - Key for music (NR)

•   s - Version (NR)

•   t - Title of a work (NR)

•   u - Affiliation (NR)

•   x - International Standard Serial Number (NR)

•   0 - Authority record control number or standard number (R)

•   3 - Materials specified (NR)

•   4 - Relator code (R)

•   5 - Institution to which field applies (NR)

•   6 - Linkage (NR)

•   8 - Field link and sequence number (R)


texts: Is DEFAULT NULL and 'nullable value without default' completely the same?

texts:
ALTER TABLE test_table ADD COLUMN a DEFAULT NULL;

vs.

ALTER TABLE test_table ADD COLUMN a;

Both columns will set NULL if column a is not specified.

As far as I know, if I add a column into a table with a default value in production database, it could lead to trouble rewriting all rows with default value.

Is DEFAULT NULL the same?


texts: Get notified when user with administrative privileges logs in

texts:
I have less than a handful of users that have sysadmin role membership.  I would like to be notified (emailed) when one of them logs in.  Is creating a trigger the best way or is there some kind of built-in function in SQL Server or maybe a SQL Server Agent-Alert that can be used?

I could build a job looking at:

SELECT name
FROM master.sys.server_principals
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1 and name not like '%NT%'

or something of that nature but that would mean the job would have to run every second...


texts: Deleting duplicates with group by and count

texts:
What is the fastest method to convert the following query:

SELECT COUNT(*) as c FROM tbl_fields
WHERE fieldnotes IS NULL
GROUP BY fieldno,fieldserial,id,fielddate,fieldsid
HAVING COUNT(*) > 1;

... into one that will delete duplicated records? The table does not have any primary keys and contains several million entries.


texts: How to return rows from multiple tables selected from information_schema

texts:
I need to return rows from a set of tables found in information_schema. I can find the table names, and I have a function that compiles and executes, but it returns an empty set, so there must be something wrong with my logic.

CREATE OR REPLACE FUNCTION get_prices()
  RETURNS TABLE(price FLOAT) AS $func$
    DECLARE
      tn TEXT;
      BEGIN
        FOR tn in SELECT table_name
          FROM information_schema.tables
          WHERE table_name ~~ '%price%'
          AND table_schema ~~ 'stg0' LOOP
            EXECUTE 'SELECT price FROM stg0.' || tn;
          END LOOP;
      END;
  $func$ LANGUAGE plpgsql;

When I execute the following

SELECT * FROM get_prices();

I get a table with no rows and a single column, price.

The SELECT in the FOR loop is returning all the needed table names. There are many rows in those tables in the price column. What should change to return those rows?

UPDATE: I should have made it more clear that I have a large, undetermined number of source tables for the query at compile time, so I was looking to query the information_schema at runtime in order to pull the source table names dynamically.

The given query works and actually returns data by replacing the EXECUTE line with

RETURN QUERY EXECUTE 'SELECT price FROM stg0.' || tn;

as suggested in the comments.


texts: Migrating to a new (upgraded) SQL server

texts:
We have a single SQL server with about 60 databases running on Win2012r2 with SQL 2014.

I am in the process of planning a hardware refresh and plan to migrate to WIN2016 with SQL 2017, I understand the basics of migrating to a new server (copy files over, re-attach, test etc).

My question is, we have specific restricted logins setup for each database.  The logins are windows ad groups and have specific permissions for their allowed databases. In addition to this we have about 20 SQL logins with the same setup against their databases.

My question is, how can i migrate these logins and databases while maintaining the permissions for specific databases?


texts: SQL Server Agent - Service Failed to Start (Access is Denied)

texts:
I have a server running Server 2012 R2 with SQL Server 2012 installed. The SQL Server Agent works fine if it is running under Local System. I changed the SQL Agent service account to a domain account and the service will not start. Checking the event viewer the only error message available is the one below.

  The SQL Server Agent (NEPO) service failed to start due to the
  following error:  Access is denied.

If I add the domain account to the local admin group on the server the service starts normally. I don't want to leave that account as a local admin. I have also gone through and manually granted all the permissions Microsoft outlines in this article. Still getting the same error and not sure where to turn next.


texts: Which database should I look at for analyzing ~30gb, 700M records dataset?

texts:
I'm looking at analyzing a kaggle dataset which is pretty normalized, specifically joining, grouping, slicing and dicing the 3 tables patients(2M) <-> diagnosis(700M) <-> doctors(2M).

I have 30GB RAM, 300GB SSD machine, so I'm able to analyze using a good old c program (I practically malloc and load everything to memory...), but I want to employ the power of ad-hoc SQL queries.

I tried different MySql, SQLLite and even mini-hadoop configurations, both on my machine and on google compute cloud and amazon EC (just to make sure that its not a config/env problem).

I can't find anything that will give me results in less than a couple of minutes to any ad-hoc query.. and if I didn't go thru the hassle of building indexes ad-hoc performance was atrocious (hours or so).

Am I missing something obvious? If its possible for my c program to fit everything in memory and do crude analysis, isn't there a database which loads everything to the buffer cache and gives lightning-fast performance for all the wonderful SQL analytics?

I know this might some a bit naive, but please go easy on me - I'm used to huge Teradata/Hadoop clusters doing heavylifting of many TB of data in very good timings, so I expected this to be very easy to handle...


texts: Better way to full text query across multiple tables?

texts:
I've got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow:

SELECT t0.[Key], t0.[Nr]
FROM dbo.[Customers_View] AS t0
 WHERE (t0.[Deleted] IS NULL)
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t1 WHERE t0.[Key] = t1.[Key] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t2 WHERE t0.[Key] = t2.[CustomerKey] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t3 WHERE t0.[Key] = t3.[CustomerKey] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t4 WHERE t0.[Key] = t4.[CustomerKey] AND CONTAINS(*, '"smith"')))
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t5 WHERE t0.[Key] = t5.[Key] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t6 WHERE t0.[Key] = t6.[CustomerKey] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t7 WHERE t0.[Key] = t7.[CustomerKey] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t8 WHERE t0.[Key] = t8.[CustomerKey] AND CONTAINS(*, '"munich"')))

Is there a good way to speed up this Kind of query? Usually it is a bit more complex, I simplyfied it to the full text part.

Maybe there is a better way to to fulltext queries across multiple tables?


texts: Migration of SQL Server to Oracle Database

texts:
In Sql server, my Procedure creates temporary tables where column names dynamically come from every user, then inserts data into temporary tables. I use the data further  based on the requirement. Finally, we drop the temporary tables.

There is no problem when we create  temporary tables with the same name and with different columns because in SQL Server the temporary tables are Session Based.

Now, I want to migrate the logic of the SQL Server code to Oracle. Is their any solution to  achieve this requirement in Oracle Database Server.

Answers are greatly appreciated.


texts:
all!
I want to know how wrote this code of SQL, I already trying but i get message error.
I wrote two way different this SQL. See you below

SELECT *
  FROM tabela
 WHERE coluna IN (CASE WHEN 1 = :parametro THEN 1 WHEN 0 = :parametro THEN (2, 3) END)

And

SELECT *
  FROM tabela
 WHERE (CASE WHEN 1 = :parametro THEN coluna = 1 WHEN 0 = :parametro THEN coluna <> 1 END)

I tried to run but it get error. Please! you help to tick or fix this code.

Thank a lot.

Regards,
Jeison Pereira


texts: How to query data in MySQL and be able to save in SQL Server? (Python)

texts:
Good day I have a doubt, I have a code generated in Python which he does is to query a MySQL table named INFORMATION_SCHEMA.PROCESSLIST as follows:

SELECT *, IF (time> = 10, "Bad", "Good") AS State FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id ();

Once this fulfills my conditions by means of python code I tell you to save those processes and also kill them with the command KILL, but the data that I send to make them in a table that creates in MySQL.

Now what I want is to continue browsing in MySQl, but I want to know if there is any way to save the queried data to a table but to SQL Server, basically query MySQL and save it to SQL Server.

Annex the code:

#! / Usr / bin / python
# - * - coding: UTF-8 - * -
Import os, sys
Import signal, os, sys
Import MySQLdb
connection_mysql = MySQLdb.connect (host = '10 .248.204.43 ', user =' root ', passwd =' sahc ', db =' Library ')

cur = conexion_mysql.cursor ()
query = ('SELECT *, IF (time> = 10,' Bad ',' Good ') AS State FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id ()')
cur.execute (query)
for row in cur.fetchall ():
    result = row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]
    sql = "INSERT INTO test VALUES (" + str (row [0]) + "," + "'" + (row [1] or' ') + "'" + "," + " [2] or "+", "+", "+", "+ (row [3] or '') +" '"+", "+" ) + "," + "," + "," + ", +", "+", "(Row [6] or '') [7] or '') + "'" + "," + "now ()" + ")"
    cur.execute (sql)
    connection_mysql.commit ()
    print "Successfully Saved Data"
    if row [0] == row [0]:
        kill = "KILL" + str (row [0])
        cur.execute
        connection_mysql.commit ()
        print "Deleted data"

As you consult and save in MySQL, I want to continue browsing in MySQL but save the data in SQL Server.

I would appreciate the help :)


texts: Creating Database Tables for Reviews functionality that incorporates Tags/Categories

texts:
I'm wanting to create some simple website functionality through a little database idea I have in order to give myself the ability to create/retrieve customer reviews based upon tags/categories. More specifically, the reviews are not actually being input by the reviewer, they are being aggregated from outside sources and manually placed in the database by myself so that I can retrieve them at later dates and in specific site sections based upon the associated tags/categories that I place on them.

Listed below are the tables and their data structures that I've come up with:

The Rating Details Table would have at least the following fields:

**********************************************************************************
| rating_details_id   | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| professional_rating | INT          | UNSIGNED | NOT NULL |                |    |
| efficiency_rating   | INT          | UNSIGNED | NOT NULL |                |    |
| referral_rating     | INT          | UNSIGNED | NOT NULL |                |    |
| rating_comment      | TEXT         |          |          |                |    |
| rating_author       | VARCHAR(200) |          | NOT NULL |                |    |
| rating_date         | DATE         |          | NOT NULL |                |    |
**********************************************************************************

The Rating Tag Table – I'm assuming – would have the following fields:

**********************************************************************************
| rating_category_id   | BIGINT(20)  | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| rating_category_name | VARCHAR(75) |          |          |                |    |
**********************************************************************************

The final Main Rating Table – I'm assuming – would then consist of:

**********************************************************************************
| rating_id           | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| rating_details_id   | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | FK |
| rating_category_id  | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | FK |
**********************************************************************************

Question 1): Do these tables look correct for what I'm trying to accomplish? If not, please provide me with some feedback as to how I should structure these tables and their data.

Question 2): If these tables are correct could you provide me with some assistance as to how I might query these tables through joins so that I could add data and retrieve date?

Example 1) How would I query the DB to insert a new review where the categories are foo and foo2?
Example 2) How would I query the DB to retrieve all reviews where the category is foo?

I hope this all makes sense and I really appreciate any and all useful insight that you might be able to offer.


texts:
I need some information on SQL Server timestamp.

I found all the required information here.

The article says

  The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Sample syntax is  to create timestamp column is

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

The new syntax is

CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;

So the question is, the word deprecated means Is Microsoft going to completely remove the timestamp datatype or it is removing just the syntax of using it ?

As I am using SQL Server 2014, in Design mode, I am still able to see timestamp datatype.

So can someone please clarify my doubt ?

And also is there any alternative datatype for timestamp, I want to use it for concurrency check ?


texts:
I am using BCP as follows,

bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n

This works 100%. I have omitted the rest of it because it is just server, user, password settings etc.

If I change the query to this,

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

With a case statement the BCP import gives this,

Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid date format
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

The contactNo field is an INT NOT NULL in both source and destination, so I cannot see why translating the value using case then casting it to INT is not working?

My BCP import looks like this (minus the server, user, password etc),

bcp GpsPoint IN "c:\temp\GPSTracking\gpspoint.dat" -n

UPDATE:

If I change the query to this it works,

SELECT id, queueid, 8022 as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

So it is definitely the case statement. And the issue does not resolve if I use ELSE 0 in the case statement. On my actual query I have a where clause (the where clause on the very first query above) which selects only 3 records as a proof of concept, and they all have 8022 as the value. The ELSE 0 never gets used if I add it and I still get the same error.


texts: How do I completely remove MySQL from a 64-bit windows 7 machine?

texts:
I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine.

I've decided to attempt to completely remove MySQL from my machine and to restart the installation process from scratch.

However, after uninstalling all software linked with MySQL using the conventional control panel uninstaller, I see there are some MySQL windows services still remaining on my machine.

I can't see any obvious method to remove these services and they have since been causing me difficulties when trying to install different versions of MySQL.

Could anyone please advise how I can remove these services?


texts: Associate information from two different sources (Oracle)

texts:
We want to implement a new Oracle Database in order to store part information. The first part would be a table with a column for an identification number (string) and another column with images (BLOB).
The identification number is read by a lector connected to a pc and available as a string of X digits. After reading the identification number of the part, an image of the same part is taken (connected to a different pc) and saved to C:\Folder.

How do I associate the identification number and the image in the table? I need the two values in the same row so that querying the blob with the identification number is possible.


texts:
Is there a naming convention for lookup tables?
I cannot see any declaration by oracle or anything consistent on google.

However I assume some of you professional DBAs follow a convention?
Perhaps there is a convention which occurs the most when you are called to edit a project you didn't author?

example table

metals

+----+------------+
| id | metal_type |
+----+------------+
| 1  |    gold    |
| 2  |    silver  |
| 3  |    bronze  |
| 4  |    brass   |
+----+------------+

Currently I have been using the original table name + the column name as my lookup table name.

metals_metal_type

I decided to change to LK_ + original table + column as it allows easier navigation when browsing all tables.

LK_metals_metal_type

What are your views on this?

Thanks


texts: AWS Aurora Postgres 9.6 major version upgrade via slony?

texts:
The official documentation states that the major in-place version upgrades are not possible.

  Note
  Aurora PostgreSQL does not currently support in-place major version upgrades. To migrate a database from one major version to another you can use dump and load tools such as the PostgreSQL utilities pg_dump and pg_restore

Reference: Upgrading an Aurora PostgreSQL DB Cluster Engine Version ()

Is there any possibility of using tools like Slony for aurora postgres major version upgrade?

I'm bit sceptical owing to the container based approach of fully managed aurora.


texts: Updating a table with a single UPDATE statement vs several UPDATE statements

texts:
CREATE TABLE `messages` (
  `mes_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'номер объявления',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время дачи/изменения объявления',
  `expire` date NOT NULL COMMENT 'Дата истечения объявления',
  `paid_top_cat` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху в разделе: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_top_main` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху на главной: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_links` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование ссылок: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_border` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование рамки: 0 - нет, 1 - запрошен, 2 - включен',
  `expire_top_cat` date DEFAULT NULL,
  `expire_top_main` date DEFAULT NULL,
  `expire_links` date DEFAULT NULL,
  `expire_border` date DEFAULT NULL,
  PRIMARY KEY (`mes_id`),
  # more fields
) ENGINE=MyISAM ;

I need to update some fields of a table dependently on some fields of the same table. Is the following in an UPDATE a good idea? paid_top_cat=IF(paid_top_cat=2 AND expire_to_cat<NOW(), 1, paid_top_cat), expire_top_cat=IF(expire_top_cat<NOW(), NULL, expire_top_cat) and so on for other expire_* and paid_* fields in a single statement.

What are arguments pro and counter to use this tricky UPDATE statements with many IF functions vs updating every column (or rather a pair of paid_* and expire_* columns) at once, so running UPDATE max 4 times?

MySQL.


texts: Is EAV an appropriate design for a collection of coexisting metadata-rich "fact estimates" on objects?

texts:
I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For example, we have a number of human and automated processes that could be attempting to ascertain an object's, say, PowerLevel, and as they report results that may (or may not) supercede each other, we'd like to track the history of these reports, along with metadata (dates, tuning parameters, code versions, etc.) from the processes. Specifically, we might want to do queries like "grouping by entity id, for each distinct attribute, find the most recent estimate" or "find all entities whose attributes had any updates that were run by codebase 573ae4".

As someone who's never actually used an entity-attribute value schema in production, this seems like a perfect use case for something like EAV, with additional metadata columns for the sources. Specifically, I'd envision a table like this:

entity_id | attribute_id | string_value | numeric_value | datetime_value | discovery_time | discovery_source | discovery_tuning_parameters | discovery_code_hash

But I've heard a lot of criticism of these types of schema, such as this answer. I'm hard pressed to find anyone using EAV on non-legacy systems, and that makes me very hesitant that I may be reinventing a very obsolete wheel...

On the other hand, the only other alternative I can think of is full NoSQL (bleh) or some unholy hybrid like:

id | power | power_current_discovery_date | power_current_discovery_source | power_history (an array of hstores?) | foobar | foobar_current_discovery_date | ...

because we'd need metadata on each attribute.

Any thoughts? Is this one of the few times that EAV is a good fit? Thanks for your help!


texts: How to perform multiple joins onto a table and then join resulting table onto multiple columns into another table


texts: How to know which columns value is different between two rows

texts:
I am trying to compare two addresses from the same ID to see whether they match. For example:

Id  Adress Code     Address
1   1               123 Main
1   2               123 Main
2   1               456 Wall
2   2               456 Wall
3   1               789 Right
3   2               100 Left

I'm just trying to figure out which column have changed value between two records having same ID. Like in above data ID ( 1, 2 ) have same address but different address code and ID 3 have different Address

Update
Expected result should be like this

Id   ColumnChanged
1    AddressCode
2    AddressCode
3    Address


texts: UPDATE n:m relation in view as array (operations)

texts:
Environment

I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The next task I have to solve is mapping n:m relations which refer to the mentioned objects.

Tables

CREATE TABLE art (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  -- more columns, irrelevant to the problem
);

CREATE TABLE tag (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false
);

CREATE TABLE art_tag (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

The view is populated with every relevant column of the main table art (articles) and another column with all tag IDs as array - aggregated with array_agg(<column>) AS <alias>.

Whenever I edit a view row I have to handle the array which resolves to the surrogate key table.

Required Operation

I was looking for a kind of array subtraction which can be explained by the following examples.

{1,2,4} - {1,2,3} = {4}
{1,2,3} - {1,2,4} = {3}

Solution

This code could be used inside the triggers (remove the clutter beforehand) to calculate all elements which have to be deleted from or inserted into the surrogate key table.

DO
$do$
DECLARE
  n bigint[] := array[1,2,3];
  o bigint[] := array[1,2,4];
  i integer;
BEGIN
  FOREACH i IN ARRAY n
  LOOP
    IF NOT (SELECT i = ANY (o)) THEN
      RAISE NOTICE 'insert: %', i; -- execute insert query
    END IF;
  END LOOP;
  FOREACH i IN ARRAY o
  LOOP
    IF NOT (SELECT i = ANY (n)) THEN
      RAISE NOTICE 'delete: %', i; -- execute delete query
    END IF;
  END LOOP;
END
$do$

resulting in:

NOTICE:  insert: 3
NOTICE:  delete: 4

which perfectly fits the expected output. Replacing the NOTICE with a suitable query should suffice. As the trigger always handles exactly one view row the second referenced id, not contained in the array, is known to the trigger function. So following the example code I got two pairs

(1,3) and (1,4)

where 1 represents the article id. The first pair is the one to insert into the surrogate key table and the second one is the one to delete.

Question

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

Qt's model view programming works pretty simple if exactly one table is affected. Otherwise I have to code more stuff. The chosen solution suites me because I like to put as much as possible into the database.


texts: Page count differences between sys.dm_db_index_physical_stats and sys.allocation_units

texts:
In doing research for index maintenance, I found some significant differences between the page counts reported by sys.dm_db_index_physical_stats and sys.allocation_units in some of the clustered indexes on one of our servers.

For one table, allocation_units reports 6706 data_pages and 15203 used_pages, while dm_db_index_physical_stats reports 4079 leaf pages and 15 non-leaf pages.

I don't know when the last time any maintenance was done on the indexes. It is possible that they have not been reorganized or rebuilt since they were initially loaded to the server. Rebuilding the index does sync the stats.

I'd be interested in knowing what caused it to get out of sync and if the extra space reported by allocation_units is actually reserved on the disk.

The query I used the following query for allocation_units. It returned 1 row of IN_ROW_DATA.

SELECT *
FROM
    sys.indexes idx
    INNER JOIN sys.partitions part
        ON idx.object_id = part.object_id
            AND idx.index_id = part.index_id
    INNER JOIN sys.allocation_units alloc
        ON part.partition_id = alloc.container_id
WHERE
    idx.type = 1
    AND idx.object_id = OBJECT_ID('dbo.Table')


texts: Moving a table to a different tablespace in Oracle using Online Redefinition Vs Alter table move tablespace

texts:
I have a table with 1 Billion records with size 300GB to move to different tablespace.
My question here is - What is the advantage of using DBMS_REDEFINITION (Online redefinition) package
over Alter table  move ;
Which one is fast and why?
Can we have DML access to table in either ways of moving the table?

Thanks in advance


texts:
This might be a silly question, but I can't for the life of me figure out where the crawl logs for my SQL Server 2012 instance are. TechNet says to look under %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG for 2008, but the LOG folder does not exist under %ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL.

I have upwards of 500,000 failed records after my last FTS index population and I need to know what the issue is.


texts:
we have a wordpress site (Centos6.5+apache2.2.15+mysql5.1.73+php5.3), which are server in vultr, I noticed there has a terrible mysql traffic.

Traffic Tip ø per hour
Received    67 MiB  88 MiB
Sent    4,634 MiB   6,106 MiB
Total   4,701 MiB   6,194 MiB

Total   ø per hour  ø per minute    ø per second
1,481 k 1.95 M  32.52 k 541.96

 Show Full Queries  ID  User    Host    Database    Command Time    Status  SQL query
Kill    311206  root    localhost   wp  Sleep   3   --- ---
Kill    311210  root    localhost   wp  Sleep   2   --- ---
Kill    311213  root    localhost   wp  Sleep   1   --- ---
Kill    311214  root    localhost   wp  Sleep   0   --- ---

sent data is 6GB per hour. I listed in show processlist; and view the slow query log, it seems no problem.

Here is my my.cnf

port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
skip-external-locking
bind-address=xx.xx.xx.xx
# I need some remote connection, but I have stopped them in crontab, then monitor again, in processlist and slow query log, all the queries have already come from localhost, but the sent traffic still looks terrible.
max_connections=2500
wait_timeout=30
back_log=256
key_buffer_size=256M
table_cache=2048
sort_buffer_size=128k
read_buffer_size=128k
net_buffer_length=128K
read_rnd_buffer_size=3M
myisam_sort_buffer_size=16M
thread_cache=64
query_cache_size=32M
bulk_insert_buffer_size=16M
ft_min_word_len=2

innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=8M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_force_recovery=0
innodb_log_file_size=96M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

httpd.conf

Timeout 45
KeepAlive Off
MaxKeepAliveRequests 256
KeepAliveTimeout 3
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 192
MaxClients 192
MaxRequestsPerChild 2500
</IfModule>

what caused such a huge mysql traffic, Is this normal?


texts: Name for a "lookup" table with values in separate rows

texts:
I am looking to put a name on a particular situation.  When you remove a comma-delimited column and split it into separate rows in a new table, what is that type of table called?

In the example below, I could keep FavoriteFoods in the top table (Albert Einstein would have "Apple Pie, Soup") but I want each food to have its own row.

Is the second table a "cross join" table that hasn't been normalized?

Please share if you know the name for this.  I will do more research once I know the name.


texts:
When I attach a database file from SQL Server 2000 Standard Edition to SQL Server 2000 Database  on another  machine ,
I am receive the following error.

"Error 5172: The heather for file 'D:...' is not a valid database file header.
 The PageAudit property is incorrect."

I can not transfer data.What should I do?


texts:
I am working on a hospital management system using DB2 with pureXML.

Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use multiple databases in order to be scalable?


texts:
I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations and I have the following:

TABLE office_settings
column office_id (bigint not null)
column setting_value (NVARCHAR(500) NOT NULL)

office_id    setting_value
--------------------------------------------
4             0.2
4             0.2
4             2
4             3
5             0.2
5             0.2
5             2
5             3

I'd like to convert this using PIVOT (assuming that's the right tool for the job) to this, basically "grouping by" the office_id column, so:

office_id    value_1    value_2    value_3   value_4
------------------------------------------------------
    4         0.2         0.2         2         3
    5         0.2         0.2         2         3

I should also mention that I need to CAST the setting_value column values to DECIMAL(4,2) in the process of this - in case that has any bearing on the T-SQL required to accomplish it.

Any ideas?


texts:
I have a database which has almost 130 tables.

I am using the Ola Hallengren maintenance solution and IndexOptimize scripts.

I have two tables in particular that in my opinion needs to be better handled:

Table1: PK INT, almost 1.5 millions of rows
Table2: PK INT, foreign Key to Table 1, almost 6.5 millions of rows

On table2 I have created an index some time ago, following an azure recommendation, like this:

CREATE NONCLUSTERED INDEX [IX_Table2_ForeignKeyField]
ON[dbo].[Table2] (
    [ForeignKeyField]
)
INCLUDE([AnotherField])
WITH( ONLINE = ON )

When the IndexOptimize runs I can see that to rebuild the IX_Table2_ForeignKeyField index the stored procedure uses the following command

ALTER INDEX [IX_Table2_ForeignKeyField]
ON [mydb].[dbo].[Table2]
REORGANIZE
WITH (LOB_COMPACTION = ON)

The results are that it takes more than two hours to reorganize this index (previous reorganization was done 3 days ago) and at the end it says that

<ExtendedInfo>
  <PageCount>644096</PageCount>
  <Fragmentation>0.752683</Fragmentation>
</ExtendedInfo>

Is there any procedure that I can use to make this command run faster?

EDIT: One more info: Table1 has from 500 to 2500 insert per day. On table2 I have almost 10k insert per day growing.


texts: MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

texts:
I use the root account created the account 'a'@'%'. But I can't use the account to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. Please see the transcript below. I hope someone can help me to explain it. Thanks.

mysql> grant all on *.* to 'a'@'%' identified by a;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a' at line 1
mysql> grant all on *.* to 'a'@'%' identified by 'a';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'a'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for a@%                                                                                |
+-----------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'a'@'%' IDENTIFIED BY PASSWORD '*667F407DE7C6AD07358FA38DAED7828A72014B4E' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@localhost ~]# mysql -h localhost -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -h 127.0.0.1 -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u a -p
Enter password:
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u a
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.5.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.17, for Linux (x86_64) using readline 5.1

Connection id:      20
Current database:
Current user:       a@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.17 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         15 days 15 hours 20 min 18 sec

Threads: 1  Questions: 40  Slow queries: 0  Opens: 41  Flush tables: 1  Open tables: 4  Queries per second avg: 0.000
--------------

mysql>

Edit:

Yes, MySQL is listening on port 3306.

[root@localhost ~]# nmap localhost

Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2012-01-18 07:35 CST
Interesting ports on localhost.localdomain (127.0.0.1):
Not shown: 1674 closed ports
PORT     STATE SERVICE
22/tcp   open  ssh
25/tcp   open  smtp
111/tcp  open  rpcbind
631/tcp  open  ipp
840/tcp  open  unknown
3306/tcp open  mysql

Nmap finished: 1 IP address (1 host up) scanned in 0.064 seconds
[root@localhost ~]#


texts: Can I convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X?

texts:
I have downloaded and installed SQL Server 2016 Community Technology Preview 2.3, which if I understand correctly, should contain a built-in function called JSON_VALUE. (Update: CTP 2.4 has shipped and no mention is made of new JSON features yet, either)

Starting with the JSON features that DO already work, the "... FOR JSON AUTO" can be specified in my SELECT statement, like this:

SELECT [ID], [DATA] FROM [dbo].[JSONDEMO] FOR JSON AUTO

The result for my demo query above will be a resultset with one column, with some auto-generatedname like JSON_F123123123 and the value in JSON format, something like:

[{"ID":"IDVALUE", "DATA":"{ {name:'ramsoft', id:1234}, ... }", ... }]

So far so good, but if DATA was containing JSON data, you would be receiving a JSON document containing a data string literal, even though that string literal is valid JSON. What you actually probably wanted, or what I wanted, rather, is:

 [{"ID":"IDVALUE", "DATA":{ {name:'ramsoft', id:1234}, ... }, ... }]

According to some MS Blog posts, to get the JSON value as a JSON value instead of as a VARCHAR(n) string you should write:

SELECT [ID], JSON_VALUE(D.DATA,'$') DATA
FROM [dbo].[JSONDEMO] D FOR JSON AUTO

This does not work, I get an error:

'JSON_VALUE' is not a recognized built-in function name.

Is some kind of JSON value extraction available in CTP 2.3, or is this not going to exist until CTP 3?


texts:
I am currently working on sharding. I have certain issues related to sharding :

1.If I load data again to a collection which is already sharded, the data stays only in one shard and not migrating to the other shard. I don't know the exact reason why this is happening?

2.I loaded data into the primary shard. When I enable sharding to that database, half of the data is present in the primary shard and the remaining data is lost . I could see the warning in the log file as :

warning: moveChunkCmd deleted data already in chunk

I don't know how to proceed with this. It will be helpful if someone help with this

My sh.status() looks like this :

shards:
        {  "_id" : "shard0000",  "host" : "172.16.2.61:27017" }
        {  "_id" : "shard0001",  "host" : "172.16.2.56:27017" }
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0001" }
        {  "_id" : "Testdatabase",  "partitioned" : true,  "primary" : "shard000
1" }
                Testdatabase.testcol
                        shard key: { "_id" : "hashed" }
                        chunks:
                                shard0001       2
                                shard0000       2
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong(
"-4611686018427387902") } on : shard0001 Timestamp(2, 2)
                        { "_id" : NumberLong("-4611686018427387902") } -->> { "_
id" : NumberLong(o) } on : shard0001 Timestamp(2, 3)
                        { "_id" : NumberLong(o) } -->> { "_id" : NumberLong("461
1686018427387902") } on : shard0000 Timestamp(2, 4)
                        { "_id" : NumberLong("4611686018427387902") } -->> { "_i
d" : { "$maxKey" : 1 } } on : shard0000 Timestamp(2, 5)
        {  "_id" : "inputdata",  "partitioned" : true,  "primary" : "shard0001"
}

Thank you,
Priya


texts: How to convert database mysql4 to mysql5

texts: I have mysql dump file named booster.dump. It was export from 4.0.27-standard mysql server. Now I want to convert this database to mysql 5.1.73-cll. How can I do it? I search about this topic in internet and I have got a lots of solution but no one work for me.


texts:
Trying to load SQL Server and SSDT-BI from Visual Studio.  Got the below error when loading SQL Server 2014 and apparently it caused the Reporting Services-Native to fail to load.

I didn't take a screen shot of the actual error but the screen looked just like the second image except the file was in the windows/temp folder as stated on the below text.

Feature:                       Reporting Services - Native
  Status:                        Failed: see logs for details
  Reason for failure:            An error occurred during the setup process of the feature.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                SQL Server Reporting Services
  Component error code:          0x84CF0003
  Error description:             Updating permission setting for folder 'C:\Windows\Temp' failed. The folder permission setting were supposed to be set to 'D:(A;OICI;0x1200af;;;S-1-5-80-425977601-1203083412-1631309457-2457533047-3321749933)'.

Do I have to get a 'succeeded' status for Report Services before I can load and use SSDT-BI/Visual Studio?  Suggestions on how to get Reporting Services to load successfully would be greatly appreciated.


texts: Select all data group by 1 column and functioned by another

texts:
I have the following table in SQL Server 2012 :

ID int
priority int
type int
other data columns

I want to query the table for the following result :

grouped by 'type'
get the id of top 1 max priority from all the grouped rows
select all rows from the id's selected in the last step

Example :

ID      Prioroty      Type           Name
1         5            3               foo1
2         4            3               foo2
3         0            4               foo3
4         0            4               foo4
5         2            64              foo5

will give the following result :

ID      Prioroty      Type           Name
1         5            3               foo1
3         0            4               foo3
5         2            64              foo5


texts:
I have a Microsoft SQL Server that had a database called SQLDATABASE. It also had a user called SQLUSER. The SQLDATABASE was deleted but not SQLUSER. SQLUSER only belonged to SQLDATABASE. I want to know all of the "Broken" users (e.g. SQLUSER) that is not part of a SQL database so that I can "clean house". I can go into every sql user and delete them by looking to see if they are attached but that seems very inefficient and something a script can help with. This is what I am asking.

The server has 200 SQL Databases but 250 SQL Users. I want to remove the extra 50 SQL Users as they aren't being used and there is no reason to have them.

I am trying to find a script to either let me find (and delete) these SQL Users or at least identify so I don't have to go into each of the SQL Users individually.


texts: How to perform strictly SQL query that uses multiple values and searches all fields

texts:
I'm trying to create what I would call an "inclusive" search and by that, I'm not referring to "between". I have a snippet of code and it does what I need it to do but it's hacky and inefficient so I'm wanting to know if I can possibly do it with just SQL. I'm using a third party library called MysqliDb but if you want to forego phrasing your answer in that format, it's fine.
        if ($this->data != '') {
        $searchParams = explode(' ', trim($this->data));
        foreach ($searchParams as $s) {
            $this->db->orWhere('customer_name', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_address', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_city', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_zip', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_email1', '%'.$s.'%', 'like');
        }
        $this->db->having('customer_status', 'Active');
        $this->db->having('owner_id', $this->owner_id);
        $binaryArray = array_fill(0, sizeof($searchParams), 0);
        $results = $this->db->get('tblcustomers');
        $filtered = [];
        foreach ($results as $r) {
            $binaryArray = array_fill(0, sizeof($searchParams), 0);
            foreach ($binaryArray as $key=>$b) {
                if (strpos(strtolower($r['customer_name']), strtolower($searchParams[$key])) !== false ||
              strpos(strtolower($r['customer_address']), strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_city'], strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_zip'], strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_email1'], strtolower($searchParams[$key])) !== false
            ) {
                    $binaryArray[$key] = 1;
                }
            }
            if (!in_array(0, $binaryArray)) {
                $filtered[] = $r;
            }
        }
    } else {
        $this->db->where('owner_id', $this->owner_id);
        $this->db->having('customer_status', 'Active');
        $filtered = $this->db->get('tblcustomers');
    }

$this->data is an input box with possible search words separated by spaces so my intent is to split the value of the input box up based on that and perform a query that has to include all of the parameters. By that I mean, I can easily do a query where it retrieves every row with $this->data[0] or $this->data[1] but I want to exclude rows that don't actually have have all of the words in $this->data in one column or another.
What I have does that but it uses a lot of PHP and I'd rather keep the db querying to SQL exclusively.


texts: Get three last movements of the per ID with LAG and GROUP BY in MySQL

texts:
I hope someone can help with this query, I have a requirement for a query that groups the id of the last date, but I want to have three columns with the 3 last movements.
So I tried grouping by the id and used MAX(date), and used LAG to get the three last movements, but I'm getting an error from (PARTITION BY id ORDER BY id..).
If I delete the two LAG functions, then the query runs. I don't know if I'm missing something there. I'm using MySQL Workbench 8.0 C.E (edit. I'm probably using another version of MySQL 5.7)
SELECT
    id,
    MAX(lastdate),
    LAG(Move, 2, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move1,
    LAG(Move, 1, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move2,
    Move AS Move3 ,
    action_ticket
FROM table
GROUP BY id

This is the table that I have:

id
lastdate
move
action ticket

12
25/02/20
up
scale

12
26/02/20
down
scale

12
27/02/20
left
solved

15
23/02/20
left
scale

15
22/02/20
up
scale

15
25/02/20
right
solved

And the table that I want to get is:

id
lastdate
move1
move2
move3
action ticket

12
27/02/20
up
down
left
solved

15
25/02/20
up
left
right
solved

Any help is really appreciated. Thanks a lot
Maria J.
Edit. Thank you very much for all the help !


texts: What is the difference between different utf8mb4 binary collations?

texts: What is the difference between utf8mb4_0900_bin vs utf8mb4_bin binary collations?


texts:
Firstly,I am new to mongodb.  when we executed the below command on mongos server.

/usr/bin/mongos --configdb X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 --port 27017 --chunkSize 1

we are getting the below error:

2014-12-01T14:40:24.947+0300 [Balancer] SyncClusterConnection connecting to [X.X.X.1:20001]
2014-12-01T14:40:24.947+0300 [Balancer] SyncClusterConnection connecting to [X.X.X.2:20002]
2014-12-01T14:40:24.948+0300 [Balancer] SyncClusterConnection connecting to [X.X.X.3:20003]
2014-12-01T14:40:24.957+0300 [Balancer] scoped connection to X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 not being returned to the pool
2014-12-01T14:40:24.957+0300 [Balancer] caught exception while doing balance: error checking clock skew of cluster X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 :: caused by :: 13650 clock skew of the cluster X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 is too far out of bounds to allow distributed locking.

Kindly let us know what it meant by. weather it is  properly connected with config server or not?


texts: How do you run T-SQL commands on multiple servers from one place?

texts: I have some daily and weekly tasks where I run a script to get the required information.  What is the way to running this code once and hitting all the servers without having to log into each one of them separately?